Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy a Named Range to a New Workbook without changing the Reference
I have a large Workbook that one person uses. There is a sheet in
this workbook that needs to be published on my company's internal website for everyone to use. We don't want to put the whole workbook on the internal site because it contains sensitive data. The worksheet that I want to publish on the internal website is called WORKNETSHEET and contains one column with names, a second column with a validation lists, the third column with vlookups based off the list selection. The source of the validation lists (refers to) are named ranges from another worksheet in the workbook called HISTORYDATA. The vlookups also reference the ranges in the HISTORYDATA sheet. So what I have done is created a macro that copies both the WORKNETSHEET and HISTORYDATA sheet and pastes them into a new workbook to be put on our internal website. My problem is that the Named Ranges and vLookups still reference the original workbook. For example. In the original workbook my one of my named ranges is =OFFSET(HistoryData!$J$2,0,0,COUNTA(HistoryData!$J :$J)-1,1) After the copy macro runs the named range is now =OFFSET('G:\Carrier Services\LTL carrier relations\Fuel Surcharge\[FSC UPDATER.xls]HistoryData'!$J$2,0,0,COUNTA('G:\Carrier Services\LTL carrier relations\Fuel Surcharge\[FSC UPDATER.xls]HistoryData'!$J: $J)-1,1) My vlookups also reference the original file on the G:\ (Although I can easily add a Find/Replace in my macro to fix these) So my Question is.. How can I copy these 2 worksheets and make them completely independent and maintain the exact path for the named ranges and vlookups? I want the Named Ranges and vLookups to point to the HISTORYDATA sheet that I pasted into the new workbook, not the original one. there must be a way!? Thanks! ~Matt |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic named range reference from external workbook | Excel Discussion (Misc queries) | |||
Copying a named range with a changing cell reference | New Users to Excel | |||
automatic range - named range give me circular reference... | Excel Discussion (Misc queries) | |||
Copy named range from file list to summary workbook | Excel Programming | |||
Changing named range reference depending on a cell's content | Excel Discussion (Misc queries) |