Importing multiple excel files into one spreadsheet

[Woofy_McWoof_Woof]

Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the previous day's data. To make it more complicated each day's file has two tabs that i need to import but I'm assuming the code below will do this automatically??? Or do i need to separate the tabs out?

Error messages displayed when I run are:

The 'Next' before the 'End Sub' creates a Compile Error:Next Without For, I'm not sure why this occurs.
If i remove the 'Next' and then run it I get run time error 438: Object doesn't support this property or method

I've copied in the code below, hopefully you can help.

Sub Mergefiles()
Dim booklist As Workbook
Dim mergeObj As Object, dirobj As Object, filesobj As Object, everyobj As Object
Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.Filesystemobject")


'change folder path of excel files here
Set dirobj = mergeObj.getfolder("H:\folder1\folder2\folder3")
Set filesobj = dirobj.filesobj
Set booklist = Workbooks.Open(everyobj)


'data is contained in columns A3:AC3 downwards, the first two rows contain headers
Range("A3:AC" & Range("A65536").End(xlUp).Row).Copy


ThisWorkbook.Worksheets(1).Activate


Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial


Application.CutCopyMode = False
booklist.Close
Next
End Sub

 

[Fluff]

How about

Sub Woofy()
    Dim Fso As Object, Fileobj As Object
    Dim Ws1 As Worksheet, ws2 As Worksheet
    
    Set Ws1 = ThisWorkbook.Sheets(1)
    Set ws2 = ThisWorkbook.Sheets(2)
    Set Fso = CreateObject("scripting.filesystemobject")
    For Each Fileobj In Fso.GetFolder("H:\folder1\folder2\folder3").Files
        With Workbooks.Open(Fileobj)
            With .Sheets(1)
                .Range("A3:AC" & .Range("A" & .Rows.Count).End(xlUp).Row).Copy
                Ws1.Range("A" & Ws1.Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
            End With
            With .Sheets(2)
                .Range("A3:AC" & .Range("A" & .Rows.Count).End(xlUp).Row).Copy
                ws2.Range("A" & ws2.Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
            End With
            Application.CutCopyMode = False
            .Close False
        End With
    Next Fileobj
End Sub

 

[Woofy_McWoof_Woof]

Thanks for your comments, I have tried the code but I'm now getting a run time error 9: subscript out of range, any thoughts?

Thinking about it, I'm assuming i would need to change the sheet names to the one's i'm using?

 

[Fluff]

Thinking about it, I'm assuming i would need to change the sheet names to the one's i'm using?

That's right.

 

[Woofy_McWoof_Woof]

Ok, I've done that but still getting the same runtime error. I've also named the two sheets in my master file to correspond to the sheet names in the daily files.

Sub Woofy()
Dim Fso As Object, Fileobj As Object
Dim Ws1 As Worksheet, ws2 As Worksheet

Set Ws1 = ThisWorkbook.Sheets(Optimised)
Set ws2 = ThisWorkbook.Sheets(Baseload)
Set Fso = CreateObject("scripting.filesystemobject")
For Each Fileobj In Fso.GetFolder("h:\folder1\folder2\folder3").Files
With Workbooks.Open(Fileobj)
With .Sheets(Optimised)
.Range("A3:AC" & .Range("A" & .Rows.Count).End(xlUp).Row).Copy
Ws1.Range("A" & Ws1.Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End With
With .Sheets(Baseload)
.Range("A3:AC" & .Range("A" & .Rows.Count).End(xlUp).Row).Copy
ws2.Range("A" & ws2.Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End With
Application.CutCopyMode = False
.Close False
End With
Next Fileobj
End Sub

Any thoughts? Thanks for your help.

 

[Fluff]

Your sheet names should be in quotes

Sheets("Optimised")

 

[mohadin]

[COLOR=#333333]With .Sheets("Baseload")[/COLOR]

as well

 

[Fluff]

All of them need the quotes.

 

[Woofy_McWoof_Woof]

Sorry, schoolboy error there. It works a treat, thank you very much!!!

 

[Fluff]

Glad to help & thanks for the feedback