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]

Your master where everything needs to be copied into is missing. Please attach.

 

[YogeshShambharkar]

@jolivanes
maste is added to the dropbox. but it can be any blank file as well. also first sheet does not need to be copied, it is just instruction.

 

[jolivanes]

I'll have a look later when I have time.
In the meantime, read the articles of these sites.

Excel Tips: Why You Should Avoid Merging Cells

Use the Center Across Selection feature to easily merge cells in Excel.

edu.gcfglobal.org

Excel: Don’t Merge Those Cells! Here is an Alternative. – Office Bytes

Stop! Do Not Merge Cells in Excel - Heres why with fixes - The Excel Club

Find out why you should not merge cells in excel, the problems merged cells can cause and how you can overcome this problem.

theexcelclub.com

 

[jolivanes]

This will copy from the 7th row on down. You might want to check all workbooks to see if that is acceptable.
If you get rid of the "Merge & Center" ranges, it will make your life a lot easier.

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 = 2 To Workbooks(wb).Sheets.Count
                Workbooks(wb).Sheets(i).UsedRange.Offset(6).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]

@jolivanes
thanks a lot. the code combined the sheets the way i wanted.
but will this copy from the 7th row on down for all sheets in Master.

 

[jolivanes]

Yes, it will.

 

[FGaxha]

Try this:
Add a module and Copy paste the below code: "Master" is the name of Tab where all data will be extracted:
NOTE: All Tabs Must have same Header

Sub Merge_Sheets()

Dim startRow, startCol, lastRow, lastCol As Long
Dim headers As Range

'Set Master sheet for consolidation
Set mtr = Worksheets("Master")

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 <> "Master" 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("Master").Activate

End Sub

 

[YogeshShambharkar]

@jolivanes
Thanks a lot for your help. this has saved me a lot of valuable time.

 

[YogeshShambharkar]

@FGaxha
Thanks for your kind help. I already got a solution from @jolivanes.

 

[jolivanes]

@YogeshShambharkar
Thanks for the update.
I strongly suggest to get rid of the "Merge & Center" ranges as they are an accident waiting to happen.
If you read the articles I posted, you know why.
Good luck