VBA to change link item (not file)

[EthanRose]

Hello everyone,

First every post as I am stuck and this board seems to have lots of helpful suggestions that nearly answer my problem. I am not proficient with VBA at all so apologies if I don't explain this well.

I have an excel workbook with about 30 worksheets which are identically formatting (each one hold staff details and has various calculations about daily rates profitability etc.).

I then have a word document into which the various tables link. I have one set up but I don't really want to set all the rest up by hand as there are tons of tables to link in for each one. I thought I could show field codes and do a replace but field codes just wont show (don't ask I have tried everything!).

Anyways what I want to do is write a macro that takes all the links in my word document and changes each one to the new worksheet. The file name is exactly the same it is only the worksheet that needs to change (so for example linking to range "a5:B10" in sheet 2 instead of in sheet 1).

I hope that makes sense. In all the explanations I have seen similar it shows changing the filepath not the worksheet. I know this is probably straightforward but with staff review day shortly I am up against it and I just cant seem to crack it!

Any help gratefully appreciated,

thanks in advance

NRG

[macropod]

Hi NRG,

Probably the best way to do this is to:
in Excel -
• name each of the ranges in Excel.
• copy & paste any portion of your Excel sheet into Word as a link.
In Word -
• make however many copies of the field you need.
• press Alt-F9 to expose the field code.
• change the range address in each of the LINK fields to one of your range names.
• press Alt-F9 to restor the field display.
• press Ctrl-A, then F9 to update the fields.