excelvba

Run-Time Error 424 when copying sheets


My apologies up front as I am new to vba code. I am having an issue with the following code. I am trying to open a file then copy all the sheets from that file to the current workbook. It currently returns the run-time error 424

Private Sub testGetFile()

Dim wb1, wb2 As Workbook
Dim sh As Worksheet
Set wb1 = ThisWorkbook
Set wb2 = Application.GetOpenFilename

For Each sh In wb2
sh.Copy After:=wb1.Sheets(wb1.Sheets.Count)
Next

End Sub

Solution

  • You are not using GetOpenFilename correctly. Since it's not a Workbook object, you cannot set it directly to a workbook.

    There are a couple other places in the code I cleaned up too.

    Option Explicit
    
    Private Sub testGetFile()
    
        Dim wb1 As Workbook
        Set wb1 = ThisWorkbook
    
        Dim fileToOpen As Variant
        fileToOpen = Application.GetOpenFilename
    
        If fileToOpen = False Then
            Exit Sub
        Else
            Dim wb2 As Workbook
            Set wb2 = Workbooks.Open(fileToOpen)
        End If
    
        Dim sh As Worksheet
        For Each sh In wb2.Worksheets
            sh.Copy After:=wb1.Sheets(wb1.Sheets.Count)
        Next
    
    End Sub