Fixing macro to copy worksheets

[dlz217]

I'm trying to create a macro that copies all worksheets from one file into an open file. XLSFile is the open file, which has the macro. I'm using the open file dialog to open FileToOpen.

I'm getting an error start at the For line saying Object Required. How can I fix this? Thanks.

Sub CopyWorkbooks()

Dim sheet As Worksheet, total As Integer


Application.ScreenUpdating = False
Application.DisplayAlerts = False


    Set XLSFile = ActiveWorkbook
    FileToOpen = Application.GetOpenFilename
If FileToOpen <> False Then
 Workbooks.Open FileToOpen
Else
 Exit Sub
End If


For Each sheet In FileToOpen.Worksheets
    total = XLSFile.Worksheets.Count
    FileToOpen.Worksheets(sheet.Name).Copy _
    after:=XLSFile.Worksheets(total)
Next sheet


End Sub

 

[steve the fish]

Its because FileToOpen is just a string not an object. Try replacing a couple of your lines with:

Set wb = Workbooks.Open(FileToOpen)
For Each sheet In wb.Worksheets

 

[dlz217]

That's great. Thanks!