# MERGE EXCEL WORKBOOS SHEETWISE



## YogeshShambharkar (Dec 7, 2022)

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 (Dec 7, 2022)

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 (Dec 7, 2022)

jolivanes said:


> 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.
> ...


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


----------



## Automatrix (Dec 7, 2022)

Power Query solution: Get data from folder.


----------



## YogeshShambharkar (Dec 7, 2022)

Automatrix said:


> 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 (Dec 7, 2022)

YogeshShambharkar said:


> 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 (Dec 7, 2022)

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 (Dec 7, 2022)

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 (Dec 8, 2022)

Automatrix said:


> 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 (Dec 8, 2022)

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?


----------



## YogeshShambharkar (Dec 7, 2022)

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.


----------



## Automatrix (Dec 8, 2022)

YogeshShambharkar said:


> 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.


You can copy the M code between the queries, and just change the source step.


----------



## YogeshShambharkar (Dec 8, 2022)

@jolivanes sorry for quoting again.....

 I am pretty new to VBA. I followed your instruction from 1st reply. Saved the code to workbook which will receive all the data. the workbook is saved in the same folder as other files which are to be merged. Sheets are in same order and have similar names in all the workbooks. Now pls guide me.


----------



## jolivanes (Dec 8, 2022)

"similar" does not do it. Exact same does.


----------



## YogeshShambharkar (Dec 8, 2022)

@jolivanes
Sheet names are exactly the same in all workbooks. If you want i can share the sheets with you.


----------



## jolivanes (Dec 8, 2022)

No leading and/or trailing spaces in names by accident?


----------



## YogeshShambharkar (Dec 8, 2022)

No leading and/or trailing spaces in the names. The files are system generated.


----------



## YogeshShambharkar (Dec 9, 2022)

@jolivanes
I can share few sheets to you so you can see if something is wrong with the sheets


----------



## YogeshShambharkar (Today at 7:00 AM)

@jolivanes
no reply from you for long time.


----------

