Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Messed up references when copying sheets from one workbook to anot
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Messed up references when copying sheets from one workbook to anot
I you want to remove the links to the other workbooks then you may want to
use pastespecial and paste values only. "Susie W" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Messed up references when copying sheets from one workbook to
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. "Joel" wrote: I you want to remove the links to the other workbooks then you may want to use pastespecial and paste values only. "Susie W" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to keep superscript and subscript when copying formula to anot | Excel Discussion (Misc queries) | |||
Copying Spreadsheet with Cell References Into A Different Workbook | Excel Discussion (Misc queries) | |||
Problems with copying and pasting dates from one worksheet to anot | Excel Discussion (Misc queries) | |||
how pdf picture appears when copying and pasting one sheet to anot | Excel Discussion (Misc queries) | |||
link between sheets get messed when master sheet is sorted | Excel Worksheet Functions |