Sub sumrows()
Dim LastRow As Long

For Each sht In Sheets
With sht
.Range("b:b").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=.Range("X1"), _
CriteriaRange:="", _
Unique:=True

LastRow = .Cells(.Rows.Count, "X").End(xlUp).Row
For Each cell In .Range("X2:X" & LastRow)
If cell.Value = "" Then Exit For
cell.Offset(0, 1) = _
Application.WorksheetFunction.SumIf( _
.Range("b:b"), "=" & cell.Value, .Range("D:D"))
Next
End With
Next sht
End Sub


"Jeff Kelly" wrote:

I have the following code which acts on one sheet. How could I get it to
loop through all sheets.


Sub sumrows()
Dim LastRow As Long

With Sheets("Jan 2009")
.Range("b:b").AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=.Range("X1"), _
CriteriaRange:="", Unique:=True

LastRow = .Cells(.Rows.Count, "X").End(xlUp).Row
For Each cell In .Range("X2:X" & LastRow)
If cell.Value = "" Then Exit Sub
cell.Offset(0, 1) = _
Application.WorksheetFunction.SumIf( _
.Range("b:b"), "=" & cell.Value, .Range("D:D"))
Next
End With

End Sub