How to copy a worksheet from one workbook to another.

[fiat2]

hello all.
Using Excel 2002.

I am trying to copy a worksheet from a workbook and I would like the formulas in this worksheet to reference the new workbook rather than the old one.

-Worksheet1 has formulas that reference WorksheetABC
-Copy Worksheet1 from Workbook1
-Insert Worksheet1 into Workbook2
-Have Worksheet1 formulas reference [Workbook2].WorksheetABC

How can I achieve this?

Thanks.

 

[SkipVought]

Use Find > Replace to change the referece to the external workbook.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[fiat2]

Skip,
It can't be that. Common sense would have made me think about that first. Any other ideas?

Sometimes the answer is right in front of you, you just don't have to overthink things.

Thanks for posting the solution and for not harping on me for missing the obvious.

Thanks again and enjoy!

 

[PHV]

It can't be that
Why ?
The Replace method search formulas, not values ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[combo]

You can copy formulas between worksheets:

Dim ws1 As Worksheet, ws2 As Worksheet
' use proper worksheet references instead
Set ws1 = ActiveSheet
Set ws2 = Application.Workbooks.Add.Worksheets(1) 
ws2.Range(ws1.UsedRange.Address).Formula = ws1.UsedRange.Formula

combo

 

[fiat2]

All,
I thank you for looking at this post but Skip's solution worked.

I guess my sarcasm didn't come across as intended.....

 

[Gavona]

Might be wrong but I thought you could also do this with Edit,Links Change Source.

Gavin