CODEWITHSUNDEEP
vbaexcel
GCC
GCC

Reputation: 295

How to move a worksheet from one workbook to another in VBA

I have tried going through what other people have asked but I cannot figure out for the life of me why my code is not working. This is what I have:

Sub Move_Sheets()

Dim PTrend As Worksheet
Dim Strend As Worksheet
Dim wb1 As Workbook
Dim wb2 As Workbook

Set wb1 = Workbooks("Workbook1.xlsb")
Set wb2 = Workbooks("Workbook2.xlsb")
Set PTrend = wb2.Worksheets("Sheet1")
Set Strend = wb2.Worksheets("Sheet2")

With wb2
    .Sheets(Array(PTrend, Strend)).Copy Before:=wb1.Sheets(7)
End With


End Sub

I am trying to move sheets from workbook 2 to workbook 1. My error occurs with my "with statement". Why is my code not working?

As always, thanks for all your guys help.

G

Upvotes: 2

Views: 4388

Answers (1)

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9976

.Sheets is expecting sheet names while you are passing sheet object.

Try it like this...

With wb2
    .Sheets(Array(PTrend.Name, Strend.Name)).Copy Before:=wb1.Sheets(7)
End With

Upvotes: 1

Related Questions