Maintaining Column References

[GaryA]

I have attached a macro enabeled worksheet that will create a Word document based on the data entered into a row.

If a user inserts a column, the offsets get messed up, and so too will the word document.

How can I dynamically maintain the column references if this occurs.

[GaryA]

Sorry, Here is the attachment.

[Ken Puls]

If I was concerned about my users mucking with the sheet, what I would do is:

• Name the header ranges. i.e. I defined a range named "rngPA" on cell A1
• Adjust your code to pick up intersects

Try defining that name first. Then make sure you have the Immediate window showing and run this code:

irow = ActiveCell.Row
Debug.Print Intersect(rows(iRow).Entirerow,Range("rngPA").EntireColumn).Address
Debug.Print Intersect(rows(iRow).Entirerow,Range("rngPA").EntireColumn).Value

I think you'll get the idea.

[GaryA]

Thanks Ken, worked like a charm!

Updated file attached.

[Ken Puls]

Excellent! So this one is sorted, yes?

[GaryA]

Yes, Thanks, much appreciated!