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

  • 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:


    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 :)

    End Sub

  • Try

    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
    Application.ScreenUpdating = True
    End Sub
  • This will work also. Change references as required

    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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!