Consolidate multiple worksheets into one

Hi gurus,
I am sure this has been done before and I just can't find it. I have a workbook that has several sheets in it with various rows of data on each sheet. I would like to have a macro to loop through all the sheets in the workbook and copy and paste the rows into a new summary sheet. The rows to be copied should only have data in Column A, in other words if Column A of a row is blank I want it skipped. Also Row 1 of every sheet contains my headers, and I am only using columns A through M. It needs to be able to copy rows even when autofilter is in use.

Thanks,
Rachael

Like this?

Sub MergeAll()
Dim ws As Worksheet
Dim LR As Long
Dim shMerge As Worksheet

Set shMerge = ActiveSheet

For Each ws In ActiveWorkbook.Worksheets
LR = shMerge.Cells(Rows.Count, "A").End(xlUp).Row

If ws.Name <> shMerge.Name Then
ws.UsedRange.Offset(1, 0).Copy
shMerge.Range("A" & LR).Offset(1, 0).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End If
Next

LR = shMerge.Cells(Rows.Count, "A").End(xlUp).Row

For i = 2 To LR
If shMerge.Range("A" & i).Value = "" Then
shMerge.Rows(i).EntireRow.Delete
End If
Next i

End Sub

Sorry Jaime that didn't work, it still copies some of the rows where column A is empty. Would it matter if my data is arranged as tables?

John,
I get a compile error on yours. Looks like it is missing an "End If" statement but when I add that it says "For without Next"

Originally Posted by raeray38501

John,
I get a compile error on yours. Looks like it is missing an "End If" statement but when I add that it says "For without Next"

I caught the error and saved it. You probably looked at it before the changes.

hi how would this code be changed to copy more than one column say column A through to column M and rows 1 to say 200.

No, now that I look at this, I believe this loop will not function properly:

For i = 2 To LR
If shMerge.Range("A" & i).Value = "" Then
shMerge.Rows(i).EntireRow.Delete
End If
Next i

Once a row is deleted, the next row that the loop should hit would be the same as the prior row number.
The way the loop works, if Row 2 had a blank cell in that column, that row would be deleted, and the entire table would shift up one row.
The next row that the loop should hit should be Row 2 again to check the next item, make sense?

I'll modify today when time allows.

I found Ron de Bruin's code to merge, which works pretty well except I would like to modify it only take the rows that have values in column A.

Sub CopyDataWithoutHeaders()
    Dim sh As Worksheet
    Dim DestSh As Worksheet
    Dim Last As Long
    Dim shLast As Long
    Dim CopyRng As Range
    Dim StartRow As Long

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    'Delete the sheet "RDBMergeSheet" if it exist
    Application.DisplayAlerts = False
    On Error Resume Next
    ActiveWorkbook.Worksheets("RDBMergeSheet").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True

    'Add a worksheet with the name "RDBMergeSheet"
    Set DestSh = ActiveWorkbook.Worksheets.Add
    DestSh.Name = "RDBMergeSheet"

    'Fill in the start row
    StartRow = 2

    'loop through all worksheets and copy the data to the DestSh
    For Each sh In ActiveWorkbook.Worksheets
        If sh.Name <> DestSh.Name Then

            'Copy header row, change the range if you use more columns
            If WorksheetFunction.CountA(DestSh.UsedRange) = 0 Then
                sh.Range("A1:M1").Copy DestSh.Range("A1")
            End If
            
            'Find the last row with data on the DestSh and sh
            Last = LastRow(DestSh)
            shLast = LastRow(sh)

            'If sh is not empty and if the last row >= StartRow copy the CopyRng
            If shLast > 0 And shLast >= StartRow Then

                'Set the range that you want to copy
                Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast))

                'Test if there enough rows in the DestSh to copy all the data
                If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
                    MsgBox "There are not enough rows in the Destsh"
                    GoTo ExitTheSub
                End If

                
                CopyRng.Copy
                With DestSh.Cells(Last + 1, "A")
                    .PasteSpecial xlPasteValues
                    Application.CutCopyMode = False
                End With

            End If

        End If
    Next

ExitTheSub:

    Application.Goto DestSh.Cells(1)

    'AutoFit the column width in the DestSh sheet
    DestSh.Columns.AutoFit

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub