Merging multiple sheets onto one master sheet

**pompeydave** · 05-29-2012, 12:00 AM

I have a spreadsheet that contains approx 40 sheets, with Columns A to L.
Each sheet contains the same header row, but from 1 to 17,000 rows.

I need to combine all 40 sheets to a new sheet, preferably without including the header row (ROW 1) from each sheet apart from the first sheet.

I have tried some code I found online, but this only merged 99 rows for each sheet, and when I played with the numbers on the Macro this jug gave me an error.

the number of rows will vary week to week and possibly the number of worksheets. Therefore I would prefer not to need to define an exact number of rows, but perhaps an upper limit would be ok.

Any ideas guys?

much apprecuiate any assistance,
Dave

**jraj1106** · 05-29-2012, 01:16 AM

Hello...

Upload a sample file...

**Trebor76** · 05-29-2012, 02:03 AM

Hi pompeydave,

Welcome to the forum!!

Try this (just make sure to change to the 'wrkConsSheet' variable to the tab where you'd like the consolidated data to appear):

Option Explicit
Sub ConsData()

    'http://www.excelforum.com/excel-programming/834105-merging-multiple-sheets-onto-one-master-sheet.html

    Dim wrkMySheet As Worksheet, _
        wrkConsSheet As Worksheet
    Dim lngLastRow As Long, _
        lngOutputRow As Long, _
        lngMyCounter As Long
        
    Application.ScreenUpdating = False
    
    Set wrkConsSheet = Sheets("Sheet1") 'Sheet (tab) name to consolidate data. Change to suit.
    
    For Each wrkMySheet In ThisWorkbook.Sheets
        If wrkMySheet.Name <> wrkConsSheet.Name Then
            lngLastRow = wrkMySheet.Range("A:L").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                If lngMyCounter = 0 Then
                    wrkMySheet.Range("A1:L" & lngLastRow).Copy Destination:=wrkConsSheet.Range("A1")
                Else
                    lngOutputRow = wrkConsSheet.Range("A:L").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
                    wrkMySheet.Range("A2:L" & lngLastRow).Copy Destination:=wrkConsSheet.Range("A" & lngOutputRow)
                End If
            lngMyCounter = lngMyCounter + 1
        End If
    Next wrkMySheet
            
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

**kvsrinivasamurthy** · 05-29-2012, 02:38 AM

Create a new sheet and Name it MAIN.Copy the header to it.
Run the macro below.

Sub CopyToMain()
Dim Sh As Worksheet
Dim Totros, Totclms As Integer

Application.DisplayAlerts = False

For Each Sh In Worksheets
If Sh.Name <> "MAIN"  Then

Totros = Sh.Range("A1").CurrentRegion.Rows.Count
Totclms = Sh.Range("A1").CurrentRegion.Columns.Count

Sh.Range("A1").CurrentRegion.Offset(1, 0).Resize(Totros - 1, Totclms).Copy
    With Sheets("MAIN")
    If .Range("A2") = "" Then
    .Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Else
    .Range("A2").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    End If
    End With
End If
Next Sh

    Application.CutCopyMode = False
Application.DisplayAlerts = True

End Sub

**pompeydave** · 05-29-2012, 04:55 PM

hi guys,

many thanks for the prompt assistance - jraj106 I could not upload a sample due to customer confidentiality.
Thanks Trebor76 I tried your macro and it looks like it has worked - 63,000 rows which is roughly what I would expect. Will validate it but all looks good, and also ran very quickly which was a nice surprise.

Thanks also to kvsrinivasamurthy for your assistance.

Dave

**pompeydave** · 05-29-2012, 06:41 PM

Data validated, spot-on - worked well with powerpivot to provide a perfect customer solution. Thanks again.

**hellopaji** · 10-09-2012, 07:56 AM

Hi All,

This is more or less what i am looking to do. I need to be able to create a master sheet which can copy rows from all other sheets on the same file. and with some means it either automatically does it every time the file is saved or if i have to run a macro or push a command button. it all works.

But since this is only my first time ever attempting to do this. i am not sure if i am even using the codes mentioned above correctly.

can someone please help me create this one file.

Thanks in advance