Messed up references when copying sheets from one workbook to anot

[Susie W]

Running Excel 2003

I have a workbook, let’s call it Source1, with one sheet (Bid Items) with a
list of bid Item Numbers, bid Item Descriptions. Each column in the Bid
Items sheet is defined with a Named Range. There is also one or more daily
sheets, which refer to the named ranges in the Bid Item sheet. In each
daily sheet, the user can select items from a drop down that refers to the
items on the Bid Item tab.

I have another workbook, call it Target Book, which is identical to the
first, but with no daily tabs, just the bid items sheet.

Here’s my problem. I would like to copy the selected daily worksheets from
workbook Source1 ( and eventually from source2 and source3…)to the Target
Book, and have the daily sheets that are copied refer to the Bid Item sheet
in the Target Book.

After the copy, some of my references are referring to the locations in the
source book, and the named ranges are defined as referring to the source book.

Sometimes I get a message saying that the named ranges already exist in the
destination workbook and would I like to refer to them, but sometime I don’t.
If I do get that message, then it’s better, but not perfect.

I am quite frustrated. Do I have to recreate the references and named
ranges in code after the copy? If anyone can shed some light on this issue,
I would appreciate it. Thanks in advance.

 

[Joel]

I you want to remove the links to the other workbooks then you may want to
use pastespecial and paste values only.

 

[Susie W]

Thanks for the quick reply. I need the links because each sheet contains
running totals of the items used, and is contanting being updated with
references to the Bid Item sheet.

I resolved this problem an hour ago by switching from the sheet Copy method
from one workbook to another (which is the code generated by recording "Edit,
Copy or move sheet") to automating a Select All, copy, go to other workbook,
insert new sheet, then pasting into the new sheet. All the information, with
the formulas, references, and named ranges, get copied to the new sheet. And
when I answer get the question : "The named range already exists in the
destination workbook, should I use it?", and i respond yes, all the
references refer to the 'bid items' sheet in the destination workbook. (now
i'll turn off the questions with displayalerts = false).

I was ready to throw this whole spreadsheet in the virtual garbage. Glad
I'm done with it.