copy WB without VBA: copied sheets link back to source WB

[Dan Williams]

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 _
before:=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]

Have you tried just copying both sheets in one .copy line:
ThisWorkbook.Worksheets(array("Sheet2","Sheet1")).Copy

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 _
before:=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.)

 

[Dan Williams]

Have you tried just copying both sheets in one .copy line:
ThisWorkbook.Worksheets(array("Sheet2","Sheet1")).Copy

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 _
     before:=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