Replace worksheet


T

thadpole

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!
 
Ad

Advertisements

T

thadpole

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top