Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 14
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Dynamic named range reference from external workbook vertblancrouge Excel Discussion (Misc queries) 2 August 7th 09 05:07 PM
Copying a named range with a changing cell reference [email protected] New Users to Excel 1 February 21st 08 08:49 AM
automatic range - named range give me circular reference... George Thorogood Excel Discussion (Misc queries) 0 February 22nd 07 08:53 PM
Copy named range from file list to summary workbook [email protected] Excel Programming 5 June 5th 06 09:16 PM
Changing named range reference depending on a cell's content cparaske Excel Discussion (Misc queries) 1 July 29th 05 07:06 PM


All times are GMT +1. The time now is 10:01 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"