MERGE EXCEL WORKBOOS SHEETWISE

[YogeshShambharkar]

Hello masters,
I have multiple workbooks, each with same number and names of sheets. i would like to combine these workbooks by sheets. Data form first sheets of all workbook to be merged/consolidated/combined in to sheet 1, data from second sheets of all workbook to be merged/consolidated/combined in to sheet 2 and so on till the last sheets. doing this manually by coping and pasting data manually is very tedious and time consuming. Is there any way to do this easy way, may be there is a macro for this.

 

[jolivanes]

Not tested. Try on copied Folder/Workbooks first. Change references as required.
Your workbook that will receive all the data has the code in it.
It has been saved in the same Folder where all the other workbooks are.
It has the sheets in the same order (from 1 to last) as all the other workbooks and the sheet names are also the same.

Sub Copy_From_All_Workbooks()
    Dim wb As String, i As Long
    Application.ScreenUpdating = False
    wb = Dir(ThisWorkbook.Path & "\*")
    Do Until wb = ""
        If wb <> ThisWorkbook.Name Then
            Workbooks.Open ThisWorkbook.Path & "\" & wb
            For i = 1 To Workbooks(wb).Sheets.Count
                Workbooks(wb).Sheets(i).UsedRange.Copy ThisWorkbook.Sheets(Sheets(i).Name).Cells(Rows.Count, 1).End(xlUp).Offset(1)
            Next i
            Workbooks(wb).Close False
        End If
        wb = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

 

[YogeshShambharkar]

Not tested. Try on copied Folder/Workbooks first. Change references as required.
Your workbook that will receive all the data has the code in it.
It has been saved in the same Folder where all the other workbooks are.
It has the sheets in the same order (from 1 to last) as all the other workbooks and the sheet names are also the same.

Sub Copy_From_All_Workbooks()
    Dim wb As String, i As Long
    Application.ScreenUpdating = False
    wb = Dir(ThisWorkbook.Path & "\*")
    Do Until wb = ""
        If wb <> ThisWorkbook.Name Then
            Workbooks.Open ThisWorkbook.Path & "\" & wb
            For i = 1 To Workbooks(wb).Sheets.Count
                Workbooks(wb).Sheets(i).UsedRange.Copy ThisWorkbook.Sheets(Sheets(i).Name).Cells(Rows.Count, 1).End(xlUp).Offset(1)
            Next i
            Workbooks(wb).Close False
        End If
        wb = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

Run-time error '9':
Subscript out of range

 

[Automatrix]

Power Query solution: Get data from folder.

 

[YogeshShambharkar]

Power Query solution: Get data from folder.

Already tried before posting. Doesnot merge data sheetwise. Combines data one sheet at a time and need to format each sheet.

 

[Automatrix]

Already tried before posting. Doesnot merge data sheetwise. Combines data one sheet at a time and need to format each sheet.

I am not sure what you mean. When you connect to a folder, it has you set up a sample sheet (for the formatting), and then uses that format on the specified sheet in all the workbooks.

 

[jolivanes]

Do not quote whole posts. Just creates unwanted clutter.
That means that some name does not exist. You don't tell us which line is yellowed out some we can't help further.

 

[YogeshShambharkar]

Workbooks(wb).Sheets(i).UsedRange.Copy ThisWorkbook.Sheets(Sheets(i).Name).Cells(Rows.Count, 1).End(xlUp).Offset(1)

this is the line yellowed out........
appreciate your help.

 

[YogeshShambharkar]

I am not sure what you mean. When you connect to a folder, it has you set up a sample sheet (for the formatting), and then uses that format on the specified sheet in all the workbooks.

Yes. It has me set up a sample sheet. And it combines the selected sheet only. but if I have 10 sheets in every workbook then I have to repete the excercise 10 times.

 

[jolivanes]

Hard to leave the "Quote" button alone?
Quote only if absolutely required and only that what is relevant.
It's a lot better to refer to a poster's name and relevant post number.

I just tried the code on 25 workbooks and it works like a charm. Are you missing sheet names somewhere?