Please Help! Copy named range to new workbook without changing references.

[Matt.Russett]

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

 

[Héctor Miguel]

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 __

 

[Matt.Russett]

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