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