linked cells - source sheetname changes

[ajm]

I need to make changes to 140 odd sheet names in my master file. each of these sheets is linked to a range of cells in a WIP report. How can I make my name changes and not lose the links to the other workbook? can you create a link to a sheets index rather than name??

 

[dafan]

If you change your sheet name in your master file any references in any subfile will change too.

For example

'[Filename]Sheet1'!A1

will change to

'[Filename]Sheet1-1'!A1

if you change Sheet1 to Sheet1-1 in Filename.

 

[ajm]

thanks dafan. perhaps you can help further. I have a list of the existing sheet names next to the new sheet names. i need to loop through each sheet, find the existing sheet name in my list, and then change it to the new name. any ideas?

 

[dafan]

This code is not complete as I'm not sure how to refer to sheetnames dynamically:
Not tested.


Say A1 houses your current sheetname, B1 is new sheetname. You have 4 sheets

For i = 1 to 4

 Cursheet = Range("A" & 1).Text
 Newsheet = Range("B" & 1).Text
 Sheets(Cursheet).Name = Newsheet
Next i

Good luck. HTH.