We help IT Professionals succeed at work.

How to use VBA to combine similarly formatted single sheet Excel files.

Medium Priority
Last Modified: 2020-03-22
I have a series of sets of Excel files. In each set, I have a group of Excel files all of the same format (same number of columns). All are single sheet outputs. I need to combine these into a single sheet excel file.  

I need to add in the name of each file in a new column after column 1, on each line. This way I know the source of the data.

I will have a directory in which are all of the source files (no other files)of one set. I want to place in this directory a NEW excel file containing the VBA that will combine these files into the NEW excel file.  

I have attached four source Excel files and a copy of the solution Excel file. I use excel 365 on Windows `10DonOHara.zip and Windows 7.

Thank you,
Don OHara
Watch Question

Peter ChanProblem resolver

1. You need to scan the folder for relevant Excel files (or if you have the list of all relevant Excel files).

2. Copy the whole sheet into the "Main" Excel file. See Extract below
Copy Worksheet to Another Workbook
So far we’ve worked with copying Sheets within a Workbook. Now we will cover examples to copy and paste Sheets to other Worbkooks. This code will copy a Sheet to the beginning of another workbook:
1      Sheets("Sheet1").Copy Before:=Workbooks("Example.xlsm").Sheets(1)
This will copy a Worksheet to the end of another Workbook.
1      Sheets("Sheet1").Copy After:=Workbooks("Example.xlsm").Sheets(Workbooks("Example.xlsm").Sheets.Count)
From this URL

Tom FarrarConsultant

Would you consider using something other than VBA?  Excel had a tool (Power Query/Get and Transform) that can combine Excel files within the same folder into one data set.  An advantage of this approach would be as more files are added to the folder the data will update.  Pretty sure the file name (or tab name) can be added into the records.
Here is a VBA. Save a new file in the same folder where the source files are and paste this macro in the file.
Sub collate()
Dim swb As Workbook
Dim twb As Workbook
Dim sws As Worksheet
Dim tws As Worksheet
Dim fname As String
Set twb = Workbooks.Add
Set tws = twb.ActiveSheet
fname = Dir(ThisWorkbook.Path & "\*.xls*")
Do While fname <> ""
    If fname <> ThisWorkbook.Name Then
        Set swb = Workbooks.Open(ThisWorkbook.Path & "\" & fname)
        Set sws = swb.ActiveSheet
        tws.Cells(tws.Rows.Count, 1).End(xlUp).Offset(1).RowHeight = sws.Rows(1).RowHeight
        tws.Cells(tws.Rows.Count, 1).End(xlUp).Offset(2, 1).Resize(sws.UsedRange.Columns(1).Rows.Count - 1).Value = Replace(fname, "." & Right(fname, Len(fname) - InStrRev(fname, ".")), "")
        sws.UsedRange.Offset(, 1).Copy tws.Cells(tws.Rows.Count, 1).End(xlUp).Offset(1, 2)
        tws.Cells(tws.Rows.Count, 1).End(xlUp).Offset(1, 2).Copy tws.Cells(tws.Rows.Count, 1).End(xlUp).Offset(1, 1)
        tws.Cells(tws.Rows.Count, 1).End(xlUp).Offset(1, 1) = "DataSource"
        sws.UsedRange.Columns(1).Copy tws.Cells(tws.Rows.Count, 1).End(xlUp).Offset(1)
    End If
    fname = Dir
End Sub

Tom FarrarConsultant

Here is the result from combining your sample files using Power Query


Thanks you for your timely and direct solution for what I asked for. I am able to quickly continue my project. It also works for CSV files?  Thanks.

Tom. Thanks for your suggestions. I am always looking for better tools. I will check into Excel Tools.

Peter,  Thank for responding and your suggestions. I did take a look at your idea, but I was not able to grasp it quickly, and I needed to get my project moving.  

Thanks everyone.
Tom FarrarConsultant

Thanks for the points, Don.  You should check out the Power Query tool.  It is embedded in Excel 2016 forward.  Very useful for solutions that only could have been with significant manual work, or only achieved with VBA.


Thanks Tom.
Just read an intro and it does look good. I will find more complete write ups.
Tom FarrarConsultant

There are also some great videos.  For example, this one is probably addresses porblems similar to your issue.