Air_Cooled_Nut
New Member
- Joined
- Oct 8, 2004
- Messages
- 36
I have an Excel workbook that is used as a template. It contains references to other Excel workbooks (data links). One example:
Here it is referencing the named range "costcenter_PGE" in the sheet "2012". If I open this template the references don't change (specifically, the sheet name), which is good as they aren't supposed to anyway.
Now, I have a master workbook that builds other end user workbooks based upon the template. The master opens the template, makes some simple changes (delete columns, for example), then saves the template as a workbook for the end user in a different directory with a different file name. The file PATH for the external references are updated to point to the same directory that the end user workbook is saved to using VBA (Workbook method .ChangeLink)
That all works well and good (I step through the code and watch the changes happen). However, when I open the newly created end user workbook the external references point to the first worksheet in the external reference by default, for example:
Instead of it looking at the "2012" sheet it defaults to the first sheet in the workbook, "Instruct". The workbooks that are being referenced have multiple sheets because they are filled in by other people and may change. So it's not feasible to have a workbook with a single sheet in it (though that would be very nice, I admit).
What's happening? Why does Excel change the original, specified sheet of "2012" and replace it with the first sheet of the workbook, "Instruct"? How can I make sure it doesn't default to the first sheet of the referenced workbook so it keeps the sheet name originally given?
Thanks!
Code:
=VLOOKUP($A18,'C:\Work\program worksheets\[AD Mktg-Com 2012 R2.xlsb][B][COLOR="#FF0000"]2012[/COLOR][/B]'!costcenter_PGE,AS$5+1,FALSE)
Here it is referencing the named range "costcenter_PGE" in the sheet "2012". If I open this template the references don't change (specifically, the sheet name), which is good as they aren't supposed to anyway.
Now, I have a master workbook that builds other end user workbooks based upon the template. The master opens the template, makes some simple changes (delete columns, for example), then saves the template as a workbook for the end user in a different directory with a different file name. The file PATH for the external references are updated to point to the same directory that the end user workbook is saved to using VBA (Workbook method .ChangeLink)
That all works well and good (I step through the code and watch the changes happen). However, when I open the newly created end user workbook the external references point to the first worksheet in the external reference by default, for example:
Code:
=VLOOKUP($A18,'C:\TESTING\program worksheets\[AD Mktg-Com 2012 R2.xlsb][B][COLOR="#FF0000"]Instruct[/COLOR][/B]'!costcenter_PGE,AS$5+1,FALSE)
Instead of it looking at the "2012" sheet it defaults to the first sheet in the workbook, "Instruct". The workbooks that are being referenced have multiple sheets because they are filled in by other people and may change. So it's not feasible to have a workbook with a single sheet in it (though that would be very nice, I admit).
What's happening? Why does Excel change the original, specified sheet of "2012" and replace it with the first sheet of the workbook, "Instruct"? How can I make sure it doesn't default to the first sheet of the referenced workbook so it keeps the sheet name originally given?
Thanks!