Replace worksheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

thadpole - 12 Dec 2007 17:49 GMT

I have Workbook-A with worksheets 'Sheet1' and 'Sheet2'. I also have
Workbook-B with 'Sheet1' and 'Sheet2'.
I want to replace Workbook-B's Sheet2 with Workbook-A's Sheet2.
Workbook-B's Sheet2 must retain the same worksheet name because there are
formulas on other sheets which refer to 'Sheet2'.
I've tried copying, deleting, and renaming like this...
Dim WBA As Workbook
Dim WBB As Workbook
Set WBA = Workbooks("Workbook-A.xls")
Set WBB = Workbooks("Workbook-B.xls")
WBA.Worksheets("Sheet2").Copy after:=WBB.Worksheets("Sheet2")
'creates a worksheet called "Sheet2 (2)" on WBB since the name already exists.
WBB.Worksheets("Sheet2").Delete
WBB.Worksheets("Sheet2 (2)").name = "Sheet2"

All the formulas which referred to Sheet2 now lose their references and they
don't restore their reference when 'Sheet2 (2)' is renamed to 'Sheet2'.
Thank you for your help!

Reply to this Message

thadpole - 12 Dec 2007 21:40 GMT

Here is an idea that actually accomplishes the end result I wanted... but not
what I had in mind...

Dim WBA As Workbook
Dim WBB As Workbook
Set WBA = Workbooks("Workbook-A.xls")
Set WBB = Workbooks("Workbook-B.xls")
WBA.Worksheets("Sheet2").Range("a1:k340").Copy _
Destination:=WBB.Worksheets("Sheet2").Range("a1:k340")
'copies everything I want from WBA Sheet2 to WBB Sheet2;
'but the formulas are linked back to WBA.
WBA.Sheets("inventory").Range("a1:k340").Formula = _
WBB.Sheets("inventory").Range("a1:k340").Formula
'copies the formulas of WBA Sheet2 to WBB Sheet2

I actually wanted to copy the whole worksheet from one workbook to another,
but that produces the problem stated below...

> I have Workbook-A with worksheets 'Sheet1' and 'Sheet2'. I also have
> Workbook-B with 'Sheet1' and 'Sheet2'.
[quoted text clipped - 14 lines]
> don't restore their reference when 'Sheet2 (2)' is renamed to 'Sheet2'.
> Thank you for your help!

Reply to this Message