Importing multiple excel files into one spreadsheet

Woofy_McWoof_Woof

New Member
Joined
Oct 7, 2016
Messages
34
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
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,587
Office Version
365
Platform
Windows
How about
Code:
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
 
Last edited:

Woofy_McWoof_Woof

New Member
Joined
Oct 7, 2016
Messages
34
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?
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,587
Office Version
365
Platform
Windows
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

New Member
Joined
Oct 7, 2016
Messages
34
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,587
Office Version
365
Platform
Windows
Your sheet names should be in quotes
Code:
Sheets("Optimised")
 

mohadin

Active Member
Joined
Mar 22, 2015
Messages
361
Office Version
2013
Platform
Windows
Code:
[COLOR=#333333]With .Sheets("Baseload")[/COLOR]
as well
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,587
Office Version
365
Platform
Windows
All of them need the quotes.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,587
Office Version
365
Platform
Windows
Glad to help & thanks for the feedback
 

Forum statistics

Threads
1,089,376
Messages
5,407,903
Members
403,169
Latest member
Luna17

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top