Combine 30+ single sheet workbooks

[nightracer]

Hi

As per the title, I have multiple single sheet workbooks, and need to compile the data from each into one single sheet, taking the data in columns A-V.
All of the workbooks have the same column headers in row 1 and are in a single folder.

The number of rows can vary from 150 to 500+, so overall I am looking at 12,000+ rows.

Ideally it would happen in order, so pick all the data from Book1, then Book2 below that, then Book3 and so on.

The number of workbooks generated that need compiling can vary from 25 to 360.

Is this possible via either a macro or an add-in menu solution?

Any pointers appreciated.

 

[Logit]

The following will accomplish what you need. You will have to edit some of the syntax in the macro (file path, cell ranges, etc).
If you believe you will require more than 65536 rows to paste your data then that range will need to be edited as well.

Paste the code into a routine module and place a command button on your Sheet1, attached to this macro.

This is at least a very good start toward your goal.

Option Explicit


Sub simpleXlsMerger()
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("C:\Users\abaig\Desktop\New folder")
    Set filesObj = dirObj.Files
    For Each everyObj In filesObj
        Set bookList = Workbooks.Open(everyObj)
        
        'change "A2" with cell reference of start point for every files here
        'for example "B3:IV" to merge all files start from columns B and rows 3
        'If you're files using more than IV column, change it to the latest column
        'Also change "A" column on "A65536" to the same column as start point
        Range("A2:IV" & Range("A65536").End(xlUp).Row).Copy
        ThisWorkbook.Worksheets(1).Activate
        
        'Do not change the following column. It's not the same column as above
        Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
        Application.CutCopyMode = False
        bookList.Close
    Next
End Sub

 

[MARK858]

If you believe you will require more than 65536 rows to paste your data then that range will need to be edited as well.

@Logit, why not use Rows.Count rather than A65536?

Range("A2:IV" & Range("A65536").End(xlUp).Row)

Becomes

Range("A2:IV" & Range("A" & Rows.count).End(xlUp).Row)

then it covers all versions.

 

[Logit]

It was an old version ... just a quick and dirty sample macro.

As indicated, some of the syntax needs to be edited. But, YES, you are correct.

Thanks for that !

OP take note.

 

[nightracer]

Many thanks Mark and Logit

If I start a new workbook to bring all of the data into, can I just run the macro in that?
I've tried and seem to get a message saying the workbook is already open when the macro has run, and do I want to re-open.

Apologies, I don't really use macro's much so maybe don't understand the steps required.

Help much appreciated.

 

[MARK858]

When you say a "new workbook" do you mean a new workbook or a workbook from the same folder?

 

[nightracer]

When you say a "new workbook" do you mean a new workbook or a workbook from the same folder?

I had created a new workbook in the same folder to use as the combined sheet, but it doesn't seem to like it?

The folder has 32 workbooks in it, 31 sets of data to combine and the 1 combined sheet, and I was trying to run the macro you gave in that combined one.
Not sure if that was where I was going wrong?

 

[MARK858]

Try moving the workbook to your desktop (i.e. outside the folder) and running the code (btw, I didn't write the code), the code is trying to re-open the workbook.

 

[nightracer]

Thanks Mark

Running it from a workbook outside of the folder, it does now cycle through all of the workbooks in the folder, but then just highlights the cell range A2:IV000 in the combined book and doesn't put any data in.


Option Explicit


Sub simpleXlsMerger()
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("C:\Users\owner\Documents\CB docs\intake\test")
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)

'change "A2" with cell reference of start point for every files here
'for example "B3:IV" to merge all files start from columns B and rows 3
'If you're files using more than IV column, change it to the latest column
'Also change "A" column on "A65536" to the same column as start point
Range("A2:IV1000" & Range("A65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(1).Activate

'Do not change the following column. It's not the same column as above
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close
Next
End Sub

My lack of knowledge of vba/marcos has beaten me.
Thank you for your time again, really appreciate it.

 

[MARK858]

As long as you have data in column A in your source workbooks (and column A is your longest column) this line

Range("A2:IV[COLOR="#FF0000"]1000[/COLOR]" & Range("A65536").End(xlUp).Row).Copy

should be

Range("A2:IV" & Range("A65536").End(xlUp).Row).Copy

or

Range("A2:IV" & Range("A" & Rows.count).End(xlUp).Row).Copy