Sorry, I'm in a bit of a rush but I think the below does what you want there or thereabouts...
Public Sub Build_Totals()
Dim wsSummary As Worksheet, ws As Worksheet
Dim lngRow As Long
On Error GoTo NoSheet
Set wsSummary = Sheets("Summary")
ResumeHere:
With wsSummary
.UsedRange.Clear
.Cells(1, "A") = "State": .Cells(1, "B") = "Total"
End With
On Error GoTo Handler
For Each ws In ActiveWorkbook.Worksheets
Select Case UCase(ws.Name)
Case "SUMMARY"
'do nothing - add sheets to be ignored to above delimited with ,
Case Else
lngRow = ws.Cells(ws.Rows.Count, "F").End(xlUp).Row
With wsSummary.Cells(ws.Rows.Count, "A").End(xlUp)
.Offset(1).Value = ws.Name
.Offset(1, 1).Value = ws.Evaluate("=SUMPRODUCT(F2:F" & lngRow & ",G2:G" & lngRow & ")")
End With
lngRow = 0
End Select
Next ws
ExitPoint:
Set wsSummary = Nothing
Exit Sub
NoSheet:
Sheets.Add.Name = "Summary"
Set wsSummary = ActiveSheet
Resume ResumeHere
Handler:
MsgBox "Error Has Occurred" & vbLf & vbLf & _
"Error Number: " & Err.Number & vbLf & vbLf & _
"Error Desc.: " & Err.Description, _
vbCritical, _
"Fatal Error"
Resume ExitPoint
End Sub
Bookmarks