Combining Data from Multiple Workbooks

[Gliffix101]

I have beaten myself up trying to find this answer. I have gotten close to what I need but not exact so hoping for some help here.

I have all files (hundreds) saved out to one folder (*.xlsm).

Each file contains six worksheets:
- Sample
- Template
- Dropdowns
- Information (data sheet, all columns are the same but row counts are different)
- Type (data sheet, all columns are the same but row counts are different)
- Metrics (data sheet, all columns are the same but row counts are different)

I am looking to combine the data from the data sheets of each workbook into one master workbook. The workbook would contain three sheets with the compiled information:
- Information
- Type
- Metrics

What I have so far -- I have code that will copy all of the worksheets from all of the files within the folder into a master workbook, and then compile those sheets into a single sheet, regardless of columns or details.

Sub MergeExcelFiles()
  Dim fnameList, fnameCurFile As Variant
  Dim countFiles, countSheets As Integer
  Dim wksCurSheet As Worksheet
  Dim wbkCurBook, wbkSrcBook As Workbook

  fnameList = Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks (*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm", Title:="Choose Excel files to merge", MultiSelect:=True)

  If (vbBoolean <> VarType(fnameList)) Then

    If (UBound(fnameList) > 0) Then
      countFiles = 0
      countSheets = 0

      Application.ScreenUpdating = False
      Application.Calculation = xlCalculationManual

      Set wbkCurBook = ActiveWorkbook

      For Each fnameCurFile In fnameList
          countFiles = countFiles + 1

          Set wbkSrcBook = Workbooks.Open(Filename:=fnameCurFile)

          For Each wksCurSheet In wbkSrcBook.Sheets
              countSheets = countSheets + 1
              wksCurSheet.Copy after:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count)
          Next

          wbkSrcBook.Close SaveChanges:=False

      Next

      Application.ScreenUpdating = True
      Application.Calculation = xlCalculationAutomatic

      MsgBox "Processed " & countFiles & " files" & vbCrLf & "Merged " & countSheets & " worksheets", Title:="Merge Excel files"
    End If

  Else
      MsgBox "No files selected", Title:="Merge Excel files"
  End If
  
Call Combine
  
End Sub

Sub Combine()
Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "Combined"
Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")
For J = 2 To Sheets.Count
Sheets(J).Activate
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next
End Sub

This works in a test environment where each workbook only contains one sheet, but fails when there is more than one worksheet in a workbook.

Any help?

 

[denzo36]

If you are combining files from multiple workbooks, you should really be using Power Query, it's much easier.

It would be too long for me to explain it here but a quick YouTube search on combining multiple files will show you.

 

[pjmorris]

As Denzo said, use Powerquery - absolutely no point whatsoever in using VBA, to do so is an utter waste of time (sorry, I think I made the point).

On the Data Ribbon, select the little drop down arrow against 'Get Data' select 'From File', 'From Folder' and then follow the prompts. Learning PQ is a real investment in your Excel skills and is equally well followed up by developing Powerpivot abilities/

 

[bobbywires]

In the Combine() nacro the lines should be:

Sheets(3).Activate
.
.
For J = 3 To Sheets.Count

Because you added the tab combined, the 1st data sheet is 3

Wonderful example thank you
When dealing with container shipments we get many inventory sheets, 1 for each container.
Many times we need to combine 20-30+ excel spreadsheets and cut and paste really got boring.
only work well if the 1st row in each sheet is a header row

 

[alansidman]

 

[bobbywires]

I appreciate the power query addon and use it myself, but in the time and knowledge it takes to implement is way above the level of our clerical staff's abilities.
Currently 1 minute to combine, sort out the garbage rows and upload the data works for us.

 

[pjmorris]

Hi Bobby, the point being made is that you as the Excel expert create the Powerquery, thereafter all your clerical staff need to do is add the new data file to the folder and click refresh. The garbage rows are removed by the query. Honest.