Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please Help! Copy named range to new workbook without changing references.
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please Help! Copy named range to new workbook without changing references.
hi, Matt !
the following code-line copies an array of sheets into a new *still-un-named* workbook... Worksheets(Array("worknetsheet", "historydata")).Copy the *new* objets (sheets AND workbook), they have its *own/independent* set of names, validation, references, etc. BUT... if there are embedded objects with macro assignments... external links will *remain* hth, and if any doubt [or further information]... would you please comment ? regards, hector. __ original post __ ... worksheet that I want to publish on the internal website ... contains one column with names, a second column with a validation lists ... with vlookups based off the list selection. ... source of the validation lists... are named ranges from another worksheet ... vlookups also reference the ranges in the HISTORYDATA sheet. ... 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 [...] ... 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!? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please Help! Copy named range to new workbook without changing references.
On Apr 2, 12:58 pm, "Héctor Miguel"
wrote: hi, Matt ! the following code-line copies an array of sheets into a new *still-un-named* workbook... Worksheets(Array("worknetsheet", "historydata")).Copy the *new* objets (sheets AND workbook), they have its *own/independent* set of names, validation, references, etc. BUT... if there are embedded objects with macro assignments... external links will *remain* hth, and if any doubt [or further information]... would you please comment ? regards, hector. __ original post __ ... worksheet that I want to publish on the internal website ... contains one column with names, a second column with a validation lists ... with vlookups based off the list selection. ... source of the validation lists... are named ranges from another worksheet ... vlookups also reference the ranges in the HISTORYDATA sheet. ... 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 [...] ... 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!? Holy Crap, that was easy! only one line of code. Thanks! you rule |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Named Range references in function formulas | Excel Worksheet Functions | |||
Copy a Named Range to a New Workbook without changing the Reference | Excel Programming | |||
Copy named range from file list to summary workbook | Excel Programming | |||
Replacing Named Range Names By Cell References in Formulas | Excel Programming | |||
Copy Formular Without Changing References Within | Excel Programming |