Extract and summarize from 100 worksheets into one

Hi Amy,

Thanks for the feedback and I'm glad it worked (almost).

I've amended the following to meet the new layout - if you need to do this in the future, the area to adjust is within the

With ActiveSheet

to

End With

block. You can use the recorder the help with the code.

HTH

Robert

Sub ConsDataByMonth()

    If MsgBox("Please click ""Yes"" if the data is to be consolidated on the " _
              & ActiveSheet.Name & " tab.", _
              vbYesNo + vbExclamation, "Data Consolidation Editor") = vbNo Then
        MsgBox "Select the tab you wish to have the data consolidated on and try again." _
            , vbInformation, "Data Consolidation Editor"
    Exit Sub
    End If

Application.ScreenUpdating = False

Dim lngLastRow As Long
Dim wSheet As Worksheet
Dim rCopy, rPaste As Range
Dim strMonth As String
    
lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    If lngLastRow > 1 Then
        ActiveSheet.Range("A2:E" & lngLastRow).ClearContents
    End If
     
    For Each wSheet In Worksheets
        If wSheet.Name <> ActiveSheet.Name Then
            With wSheet
                Set rCopy = .Range("A2", .Cells(Rows.Count, 8).End(xlUp))
            End With
            Set rPaste = ActiveSheet.Cells(Rows.Count, 1).End(xlUp)(2, 1)
            rCopy.Copy
            rPaste.PasteSpecial Paste:=xlValues, Paste:=xlPasteFormats
            Application.CutCopyMode = False
        End If
    Next wSheet

strMonth = "6" 'Calendar month (i.e. June in this case) filter - _
               change as required.  See code line noted below.

With ActiveSheet
    .Range("B:B,E:E").NumberFormat = "m/d/yy"
    .Range("D:D,F:F,H:H").NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
    lngLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
    .Range("I2").Formula = "=MONTH(B2)"
    .Range("I2").Copy .Range("I3:I" & lngLastRow)
    .AutoFilterMode = False
    .Columns("I").AutoFilter Field:=1, Criteria1:="<>" & strMonth 'Month filter
    .Rows("1").EntireRow.Hidden = True
    .Columns("I").SpecialCells(xlCellTypeVisible).EntireRow.Delete
    .AutoFilterMode = False
    .Rows("1").EntireRow.Hidden = False
    .Columns("I").Delete
    .Columns("A:H").AutoFit
End With

Application.ScreenUpdating = True

ActiveSheet.Range("A1").Select

Select Case (strMonth)
    Case "1"
        MsgBox "January's data has now been consolidated." _
            , vbInformation, "Data Consolidation Editor"
    Case "2"
        MsgBox "February's data has now been consolidated." _
            , vbInformation, "Data Consolidation Editor"
    Case "3"
        MsgBox "March's data has now been consolidated." _
            , vbInformation, "Data Consolidation Editor"
    Case "4"
        MsgBox "April's data has now been consolidated." _
            , vbInformation, "Data Consolidation Editor"
    Case "5"
        MsgBox "May's data has now been consolidated." _
            , vbInformation, "Data Consolidation Editor"
    Case "6"
        MsgBox "June's data has now been consolidated." _
            , vbInformation, "Data Consolidation Editor"
    Case "7"
        MsgBox "July's data has now been consolidated." _
            , vbInformation, "Data Consolidation Editor"
    Case "8"
        MsgBox "August's data has now been consolidated." _
            , vbInformation, "Data Consolidation Editor"
    Case "9"
        MsgBox "September's data has now been consolidated." _
            , vbInformation, "Data Consolidation Editor"
    Case "10"
        MsgBox "October's data has now been consolidated." _
            , vbInformation, "Data Consolidation Editor"
    Case "11"
        MsgBox "November's data has now been consolidated." _
            , vbInformation, "Data Consolidation Editor"
    Case "12"
        MsgBox "December's data has now been consolidated." _
            , vbInformation, "Data Consolidation Editor"
    Case Else
        MsgBox "The ""strMonth"" variable has not been set correctly." & _
        " Reset it with a string value of 1 to 12 (inclusive) and try again." _
            , vbCritical, "Data Consolidation Editor"
    End Select
    
End Sub

Hi Robert,

This is awesome! I tested it and this is exactly I want! I can't express how much I should appreciate for your code coz it's gonna save tons of my OT work for the following week.

Thanks again and have a wonderful weekend!

Amy

Dear Amy,

Thanks for the feedback and you're very welcome - I'll send you the bill

I hope you have a good weekend too.

Kind regards,

Robert

Sure, any time.