Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet.copy changes references
Hi everybody,
We've got a problem with a macro in Excel. The macro copies 2 worksheets to a new document. Worksheet A has some formulas referencing cells on Worksheet B. First B is copied to the new workbook, then A. The formulas on the copied worksheet A are now referencing the original cell of worksheet B, instead of the copied ones in the same workbook. Can anybody tell me how to keep the references within the same workbook? Thanks in advance, Marco. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet.copy changes references
If the addresses in the copied "worksheet B" are exactly identical to the
new addresses A1 = A1, then you could use local addresses. NewAddress = Myrange.Address(false,false). ActiveCell.Formula = NewAddress You might have to write formulas like = NewAddress1 & "+" & NewAddress2 Ole Michelsen "Marco" wrote in message ... Hi everybody, We've got a problem with a macro in Excel. The macro copies 2 worksheets to a new document. Worksheet A has some formulas referencing cells on Worksheet B. First B is copied to the new workbook, then A. The formulas on the copied worksheet A are now referencing the original cell of worksheet B, instead of the copied ones in the same workbook. Can anybody tell me how to keep the references within the same workbook? Thanks in advance, Marco. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet.copy changes references
One option is to copy both worksheets at the same time.
Another is to convert your formulas to text, copy the worksheet, and change the text formulas back to formulas Essentially... edit|replace what: = with: $$$$$$= replace all copy the sheet(s) then edit|replace what: $$$$$$= with: = replace all One more option is to point at your new workbook: Edit|links|change source Marco wrote: Hi everybody, We've got a problem with a macro in Excel. The macro copies 2 worksheets to a new document. Worksheet A has some formulas referencing cells on Worksheet B. First B is copied to the new workbook, then A. The formulas on the copied worksheet A are now referencing the original cell of worksheet B, instead of the copied ones in the same workbook. Can anybody tell me how to keep the references within the same workbook? Thanks in advance, Marco. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy of a Worksheet Does Not Update Chart References | Excel Worksheet Functions | |||
how do I copy R[40]C1 references | Excel Worksheet Functions | |||
Copy worksheet with row & column references | Excel Discussion (Misc queries) | |||
copy/Paste references other worksheet | Excel Worksheet Functions | |||
Copy worksheet without absolutle references | Excel Worksheet Functions |