Auto collect all data from multiple sheet to one master sheet

Try this macro

Sub Consolidate()
Dim Sheet As Worksheet
Sheets("TotalSummary").Rows("5:" & Rows.Count).ClearContents
For Each Sheet In Sheets
    If Sheet.Name <> "TotalSummary" Then
        Sheets("TotalSummary").Cells(Rows.Count, 2).End(xlUp).Offset(1, -1) = Left(Sheet.Name, Len(Sheet.Name) - 2)
        Sheet.Range(Sheet.Cells(5, 2), Sheet.Cells(Sheet.Rows.Count, 2).End(xlUp).Offset(0, 5)).Copy Destination:=Sheets("TotalSummary").Cells(Rows.Count, 2).End(xlUp).Offset(1, 0)
    End If
Next Sheet
End Sub

Open up the VBA editor by hitting ALT F11

Insert a new module by hitting Insert - Module

Paste the macro into the empty sheet

Hit ALT F11 to get back to the worksheet.

Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007/2010.

Hi,
One more query. I have VisitorEntrySheet which is not be displayed in TotalSummary Sheet. How can I stop fetching data from that particular Sheet(s) - Now its catching all the data even if i created new sheet front or back (any where). Thank you

Try (you might need to make some adjustment to make the month entry as I am kinda lazy to do it right now, but the macro consolidates all the data into the summary sheet)

Sub Consolidate()
Dim ws As Worksheet, wsSummary As Worksheet
Dim i As Integer

Set wsSummary = Worksheets("TotalSummary")
With wsSummary
    .Range("A5:G" & .Cells(Rows.Count, 2).End(xlUp).Row).ClearContents
End With

For i = 1 To Sheets.Count
    With Worksheets(i)
        If .Name <> "TotalSummary" Then
            .Range("A5:G" & .Cells(Rows.Count, 2).End(xlUp).Row).Copy _
                wsSummary.Cells(wsSummary.Cells(Rows.Count, 2).End(xlUp).Row + 1, 1)
        End If
    End With
Next i

End Sub

Try

Sub Consolidate()
Dim Sheet As Worksheet
Sheets("TotalSummary").Rows("5:" & Rows.Count).ClearContents
For Each Sheet In Sheets
    If Sheet.Name <> "TotalSummary" and Sheet.Name <> "VisitorEntrySheet" Then
        Sheets("TotalSummary").Cells(Rows.Count, 2).End(xlUp).Offset(1, -1) = Left(Sheet.Name, Len(Sheet.Name) - 2)
        Sheet.Range(Sheet.Cells(5, 2), Sheet.Cells(Sheet.Rows.Count, 2).End(xlUp).Offset(0, 5)).Copy Destination:=Sheets("TotalSummary").Cells(Rows.Count, 2).End(xlUp).Offset(1, 0)
    End If
Next Sheet
End Sub