VBA - can't find the runtime error -2147221080 (800401a8)

[koskesh]

I have a macro to import 5 workbooks into one (and rename them).
The debugger stops after importing the first file. The wb2 part gets a runtime error: -2147221080 (800401a8)

Sub ImportFiles()
    ChDrive "X"
    ChDir "X:\Test"
       
    Dim wb1 As Workbook, wb2 As Workbook, wb3 As Workbook, wb4 As Workbook, wb5 As Workbook
    Dim Ret1, Ret2, Ret3, Ret4, Ret5
    Set wb1 = ActiveWorkbook
    '~~> Get the first File
    Ret1 = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", _
    , "Opening Stock ZR141")
    If Ret1 = False Then Exit Sub
    '~~> Get the 2nd File
    Ret2 = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", _
    , "Closing Stock ZR141")
    If Ret2 = False Then Exit Sub
    
     '~~> Get the 3rd File
    Ret3 = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", _
    , "MB5B Opening Stock")
    If Ret3 = False Then Exit Sub
    
        '~~> Get the 4th File
    Ret4 = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", _
    , "MB5B Closing Stock")
    If Ret4 = False Then Exit Sub
    
           '~~> Get the 5th File
    Ret5 = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", _
    , "Masterdata")
    If Ret5 = False Then Exit Sub
    
     
    'Change name and open workbooks
     Set wb1 = Workbooks.Open(Ret1)
    wb1.Sheets(1).copy wb1.Sheets(1)
    ActiveSheet.Name = "ZR141 Opening Stock"
    wb1.Close savechanges:=False

    Set wb2 = Workbooks.Open(Ret2)
   [B]wb2.Sheets(1).copy wb1.Sheets(2) <- runtime error!
[/B] ActiveSheet.Name = "ZR141 Closing Stock"
    wb2.Close savechanges:=False
  
    Set wb3 = Workbooks.Open(Ret3)
    wb3.Sheets(1).copy wb1.Sheets(3)
    ActiveSheet.Name = "MB5B Opening Stock"
    wb3.Close savechanges:=False
    
    Set wb4 = Workbooks.Open(Ret4)
    wb4.Sheets(1).copy wb1.Sheets(4)
    ActiveSheet.Name = "MB5B Closing Stock"
    wb4.Close savechanges:=False
    
    Set wb5 = Workbooks.Open(Ret5)
    wb5.Sheets(1).copy wb1.Sheets(5)
    ActiveSheet.Name = "Masterdata"
    wb5.Close savechanges:=False
    
    Set wb2 = Nothing
    Set wb3 = Nothing
    Set wb4 = Nothing
    Set wb5 = Nothing
    Set wb1 = Nothing
    
   End Sub

The runtime error occurs here:
Set wb2 = Workbooks.Open(Ret2)
wb2.Sheets(1).copy wb1.Sheets(2) <- runtime error!
ActiveSheet.Name = "ZR141 Closing Stock"
wb2.Close savechanges:=False

Hope someone can fix this annoying error for me. Thank you.

 

[koskesh]

Tried to replace i with 1. Same error

 

[Andrew Poulsom]

Are you sure that this is working as expected?

Set wb = Workbooks.Open(Ret(i))

Is wbAkt protected?

 

[koskesh]

I had to replace i with 1. A co-worker has no issues with the code now, but I can't run it on my pc.

 

[Andrew Poulsom]

In the code you posted why is copy in lower case? It should automatically change to Copy. Do you have a macro named copy?

 

[koskesh]

Found a macro name copy. Deleted it. Worked. Tried again -> same error

 

[Andrew Poulsom]

If it works after deleting the copy macro I don't know why it would fail thereafter, sorry.

 

[koskesh]

You helped me a lot. Thank you.
I found a way to use the macro. I copied the code into the workbook instead of my private macro workbook. And it works. I still have an issue with the opening of the last workbook though.
The Masterdata workbook is empty (The sheet exists with the correct name) but without the data.

 

[Andrew Poulsom]

I don't know why that would be unless the first worksheet in the original workbook was empty.

 

[corquando]

Hi, everybody.

I had this problem when I imported a sheet from another workbook via "Move/Copy", and on the same workbook had another sheet that had data taken from a SQL run.

I do not know why, but when I deleted the page that had been moved, and then simply copy-n-pasted-values-only the same data from the other workbook, the issue was resolved.

0_o.

It worked for me, so maybe it'll work for you.