Excel VBA for merging multiple sheets


New Member
Jan 23, 2015
Office Version
  1. 2016
  1. Windows
Hi Everyone!

Thank you very much for all the help you extend to people like me. I do truly appreciate it!

I have been working on moving some data from multiple excel workbooks into one excel workbook. my goal was the following:

1- Copy data from 16 different excel workbooks (only the visible sheets) into one workbook and each workbook gets copied into a different sheet (so we will be having 16 sheets).
2- all of the above gets saved into new sheet that can be saved anywhere.
3- in the new workbook, I want to add one more sheet named "Summary" that can combine all the 16 sheets (using the header in the 1st column in any sheets), then it copies any information from the subsequent sheets

I am using the below code, but I don't know what wrong did I do. The merging of all the files into one workbook works perfectly and the summary code works perfectly as its own. but when I merge them together I don't get the results I want. Could anybody help me please?

VBA Code:
[B]'Assign this macro to your button

Sub mergeFiles()


 'Merges all files in a folder to a main file.


 'Define variables:

 Dim numberOfFilesChosen, i As Integer

 Dim tempFileDialog As FileDialog

 Dim mainWorkbook, sourceWorkbook As Workbook, ResultsWorkbook As Workbook

 Dim tempWorkSheet As Worksheet

 Dim startRow, startCol, lastRow, lastCol As Long

 Dim headers As Range



 Set mainWorkbook = Application.ActiveWorkbook

 Set tempFileDialog = Application.FileDialog(msoFileDialogFilePicker)

 Set ResultsWorkbook = Application.Workbooks.Add


 'Allow the user to select multiple workbooks

 tempFileDialog.AllowMultiSelect = True


 numberOfFilesChosen = tempFileDialog.Show


 'Loop through all selected workbooks

 For i = 1 To tempFileDialog.SelectedItems.Count


 'Open each workbook

 Set sourceWorkbook = Workbooks.Open(Filename:=tempFileDialog.SelectedItems(i))


 'Copy each worksheet to the end of the main workbook

 For Each tempWorkSheet In sourceWorkbook.Worksheets

 If tempWorkSheet.Visible = True Then

 With ResultsWorkbook

 tempWorkSheet.Copy after:=.Sheets(.Sheets.Count)

 End With

 End If

 Next tempWorkSheet


 'Close the source workbook

 sourceWorkbook.Close SaveChanges:=False

 Next i


 ResultsWorkbook.Close SaveChanges:=True


 Sheets.Add.Name = "Summary"



'Set Master sheet for consolidation

Set mtr = Worksheets("Summary")


Set wb = ThisWorkbook

'Get Headers

Set headers = Application.InputBox("Select the Headers", Type:=8)


'Copy Headers into master

headers.Copy mtr.Range("A1")

startRow = headers.Row + 1

startCol = headers.Column


Debug.Print startRow, startCol

'loop through all sheets

For Each ws In wb.Worksheets

 'except the master sheet from looping

 If ws.Name <> "Summary" Then


 lastRow = Cells(Rows.Count, startCol).End(xlUp).Row

 lastCol = Cells(startRow, Columns.Count).End(xlToLeft).Column

 'get data from each worksheet and copy it into Master sheet

 Range(Cells(startRow, startCol), Cells(lastRow, lastCol)).Copy _

 mtr.Range("A" & mtr.Cells(Rows.Count, 1).End(xlUp).Row + 1)

 End If

Next ws



End Sub[/B]

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back