VBA - Copy sheet from closed workbook, run-time error

Wilhelm · Apr 8th 2020

Dear forum! I'm new to VBA but and this is my first post here

What I'm trying to do is to copy a sheet from a closed workbook to my active workbook.

When I run the macro I receive an error message (run-time error '-2147221080 (800401a8)'), however it works the second time.

The "error line" when debugging is:

closedBook.Sheets("INSERT_Arbetsgivaravgifter").Copy y.Sheets(1)

When formulation my code I got expired from:

https://www.automateexcel.com/vba/copy-worksheet/

However, instead of using "ThisWorkbook", I'm trying to use active workbook instead since the vba code is in another workbook.

Thanks in advance for any help

Code

Sub Copy sheet from closed workbook()
Application.ScreenUpdating = False

Dim y As Workbook
Set y = ActiveWorkbook

    Set closedBook = Workbooks.Open("C:\XXXX\XXXX\XXXX.xlsx")
    closedBook.Sheets("Sheet1").Copy y.Sheets(1)
    closedBook.Close SaveChanges:=False
    
Application.ScreenUpdating = True
End Sub

Display More

End Sub

jolivanes · Apr 9th 2020

Try

Code

Sub Maybe()
Dim wb1 As Workbook, wb2 As Workbook
Application.ScreenUpdating = False
Set wb1 = ActiveWorkbook
Set wb2 = Workbooks.Open("C:\Folder A\File Name To Copy From.xlsm")
wb2.Sheets(1).Copy After:=wb1.Sheets(wb1.Sheets.Count)
wb2.Close False
Sheets(1).Select
Application.ScreenUpdating = True
End Sub

jolivanes · Apr 9th 2020

This will work also. Change references as required

Code

Sub Maybe()
Workbooks.Open("C:\Test Folder\Test File.xlsm").Sheets(1).Copy After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
Workbooks("Test File.xlsm").Close False
End Sub

VBA - Copy sheet from closed workbook, run-time error

Participate now!

Share

Tags