Copy a Named Range to a New Workbook without changing the Reference

M

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top