Excel VBA for merging multiple sheets

[mrnassaro]

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?

[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

 ws.Activate

 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

 

Worksheets("Summary").Activate

End Sub[/B]