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.
Bookmarks