Running Excel 2003

I have a workbook, lets 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.

Heres 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 dont.
If I do get that message, then its 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.