Maintaining Column References

GaryA

New member
Joined
Apr 6, 2012
Messages
51
Reaction score
0
Points
0
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.
 
Sorry, Here is the attachment.
 

Attachments

  • ColRef.zip
    21 KB · Views: 19
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:
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. :)
 
Thanks Ken, worked like a charm!

Updated file attached.
 

Attachments

  • ColRef2.zip
    22.6 KB · Views: 12
Back
Top