Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy WB without VBA: copied sheets link back to source WB
The formula for cell A1 in Sheet1 is =Sheet2!A1
....and I copy Sheet2 and Sheet1 into a new workbook ThisWorkbook.Worksheets("Sheet2").Copy ' creates new WB ThisWorkbook.Worksheets("Sheet1").Copy _ befo=ActiveWorkbook.Worksheets("Sheet2") Unfortunately, the formula for the new cell A1 in the new Sheet1 is =[<original workbook]Sheet2!A1 I want the formula to be =Sheet2!A1 as it was before, referring to the same workbook it's in. This is a new problem that appeared when we switched from Excel 2000 to Excel 2003. In Excel 2000, it worked the way I wanted it to. Is there some elegant way to inhibit this linking back to a copied- from workbook? ________________________ In this case, since there was only one cell involved, I've solved it by having VBA explicitly set Range("A1").Formula, but what if there were lots of cells involved? The reason I don't copy the whole workbook at once is that I don't want to copy the VBA. (Stripping the VBA out after the fact is such a pain when doing any debugging!) So a broader question is, what's a good approach for using a VBA executable that contains template sheets that should be copied into the new workbook it creates? (Without having any VBA in the resulting new workbook, and without these sheet references linking back to the original executable WB.) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy WB without VBA: copied sheets link back to source WB
Have you tried just copying both sheets in one .copy line:
ThisWorkbook.Worksheets(array("Sheet2","Sheet1")). Copy Dan Williams wrote: The formula for cell A1 in Sheet1 is =Sheet2!A1 ...and I copy Sheet2 and Sheet1 into a new workbook ThisWorkbook.Worksheets("Sheet2").Copy ' creates new WB ThisWorkbook.Worksheets("Sheet1").Copy _ befo=ActiveWorkbook.Worksheets("Sheet2") Unfortunately, the formula for the new cell A1 in the new Sheet1 is =[<original workbook]Sheet2!A1 I want the formula to be =Sheet2!A1 as it was before, referring to the same workbook it's in. This is a new problem that appeared when we switched from Excel 2000 to Excel 2003. In Excel 2000, it worked the way I wanted it to. Is there some elegant way to inhibit this linking back to a copied- from workbook? ________________________ In this case, since there was only one cell involved, I've solved it by having VBA explicitly set Range("A1").Formula, but what if there were lots of cells involved? The reason I don't copy the whole workbook at once is that I don't want to copy the VBA. (Stripping the VBA out after the fact is such a pain when doing any debugging!) So a broader question is, what's a good approach for using a VBA executable that contains template sheets that should be copied into the new workbook it creates? (Without having any VBA in the resulting new workbook, and without these sheet references linking back to the original executable WB.) -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy WB without VBA: copied sheets link back to source WB
On Dec 28, 12:09*pm, Dave Peterson wrote:
Have you tried just copying both sheets in one .copy line: ThisWorkbook.Worksheets(array("Sheet2","Sheet1")). Copy Dan Williams wrote: The formula for cell A1 in Sheet1 is =Sheet2!A1 ...and I copy Sheet2 and Sheet1 into a new workbook * * ThisWorkbook.Worksheets("Sheet2").Copy * ' creates new WB * * ThisWorkbook.Worksheets("Sheet1").Copy _ * * *befo=ActiveWorkbook.Worksheets("Sheet2") Unfortunately, the formula for the new cell A1 in the new Sheet1 is =[<original workbook]Sheet2!A1 I want the formula to be =Sheet2!A1 as it was before, referring to the same workbook it's in. This is a new problem that appeared when we switched from Excel 2000 to Excel 2003. *In Excel 2000, it worked the way I wanted it to. Is there some elegant way to inhibit this linking back to a copied- from workbook? ________________________ In this case, since there was only one cell involved, I've solved it by having VBA explicitly set Range("A1").Formula, but what if there were lots of cells involved? The reason I don't copy the whole workbook at once is that I don't want to copy the VBA. *(Stripping the VBA out after the fact is such a pain when doing any debugging!) So a broader question is, what's a good approach for using a VBA executable that contains template sheets that should be copied into the new workbook it creates? *(Without having any VBA in the resulting new workbook, and without these sheet references linking back to the original executable WB.) -- Dave Peterson- Hide quoted text - - Show quoted text - That does the trick! Thanks!! Dan Williams danwPlanet |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Sheets to RAM and back | Excel Programming | |||
how to link data from one source sheet to multiple sheets | Excel Worksheet Functions | |||
How to Change Source Data after copy sheets from another workbook? | Excel Programming | |||
Copy tabs(sheets) from workbook without link to original source | Excel Discussion (Misc queries) | |||
Copy tabs(sheets) from workbook without link to original source | Links and Linking in Excel |