CODEWITHSUNDEEP
excelvbacopy
user26601
user26601

Reputation: 13

Copying from one workbook to open workbook

I am trying to copy an object from a closed workbook to the currently open workbook, the code I have bee experimenting with is:

Sub test()
Dim WB1 As Workbook
Dim WBDest As Workbook

Set WBDest = Workbooks(ActiveWorkbook.Path & "\" & ActiveWorkbook.Name)

 'Open up your first workbook, copy data
Set WB1 = Workbooks.Open("path to the folder\testbook.xlsx")
WB1.Sheets("Sheet1").Range("A1:F12").Copy

'paste in second workbook
WBDest.Sheets("Sheet1").Range("A1").PasteSpecial
 'Close first workbook
WB1.Close savechanges:=False
End Sub

I keep getting a "subscript out of range" error with this, if I remove the WBDest info and used activeworkbook instead, it copies the object and pastes it in the same workbook as it is the activeworkbook at the time.

Could someone please guide me on this and help me figure out what I am doing wrong.

Thanks.

Upvotes: 0

Views: 260

Answers (1)

114
114

Reputation: 926

As mentioned by AndyG, it should be WBDest = Workbooks.Open(..). The replacement is then:

Sub test()
Dim WB1 As Workbook
Dim WBDest As Workbook

Set WBDest = Workbooks.Open(ActiveWorkbook.Path & "\" & ActiveWorkbook.Name)

 'Open up your first workbook, copy data
Set WB1 = Workbooks.Open("path to the folder\testbook.xlsx")
WB1.Sheets("Sheet1").Range("A1:A7").Copy

'paste in second workbook
WBDest.Sheets("Sheet1").Range("A1:A7").PasteSpecial
 'Close first workbook
WB1.Close savechanges:=False
End Sub

Note that on the 5th line you could as easily write WBDest = ActiveWorkbook if the workbook is already open as you suggest.

Upvotes: 1

Related Questions