Sum mixed colums in multiple worksheets

[Guest]

OK Here we go... 5 worksheets each worksheet is a week of data. Colum A has
widget numbers that corrispond to three other columns that tell you how many
widgets as the other two colums also require totaling. If all of the weekly
data was in one sheet I could pivit the data. But the kicker her is not only
does the data reside in seperate sheets but the A colum is not in the same
order of each widget. So sheet number one may have widget 100 first and
sheet two may have widget 100 third. Whew...

 

[Ken Wright]

Just combine the data into a new single sheet for summary purposes and leave
the sheets alone. For just 5 sheets it's a 30 second job to do it manually,
but if you wanted code then assuming no other sheets in the workbook

Sub SummaryCombineMultipleSheets()

Dim SumWks As Worksheet
Dim sd As Worksheet
Dim Sht As Long
Dim lrow1 As Long
Dim lrow2 As Long
Dim StRow As Long

HeadRow = InputBox("What row are the Sheet's data headers in?")
DataRow = HeadRow + 1

On Error Resume Next
Application.DisplayAlerts = False
Sheets("Summary Sheet").Delete
Application.DisplayAlerts = False
On Error GoTo 0

Set SumWks = Worksheets.Add

With SumWks
.Move Before:=Sheets(1)
.Name = "Summary Sheet"
Sheets(2).Rows(HeadRow).Copy .Range("1:1")
Columns("A:A").Insert Shift:=xlToRight
Range("A1").Value = "INDEX"
End With

With Sheets(2)
ColW = .UsedRange.Column - 1 + .UsedRange.Columns.Count

End With

For Sht = 2 To ActiveWorkbook.Sheets.Count
Set sd = Sheets(Sht)
lrow1 = SumWks.Cells(Rows.Count, "B").End(xlUp).Row
lrow2 = sd.Cells(Rows.Count, "A").End(xlUp).Row

With sd
'.Activate
.Range(.Cells(DataRow, 1), .Cells(lrow2, ColW)).Copy
SumWks.Cells(lrow1 + 1, 2)
SumWks.Cells(lrow1 + 1, 1).Resize(lrow2 - (DataRow - 1), 1).Value =
..Name
End With
Next Sht

SumWks.Activate

End Sub