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.)

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 _
> 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

On Dec 28, 12:09*pm, Dave Peterson <peter...@verizonXSPAM.net> 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 _
> > * * *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