Loop through worksheets

[Jeff Kelly]

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"))
Next
End With

End Sub

 

[Joel]

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"))
Next
End With
Next sht
End Sub

 

[r]

Sub sumrows()
Dim LastRow As Long
Dim sh As Worksheet

For Each sh In ThisWorkbook.Worksheets
With sh
.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"))
Next
End With
Next
End Sub

regards
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/index.php/Excel-VBA/UsedRange-eccezioni-e-alternative.html

 

[Rick Rothstein]

See if this way works for you...

Sub SumRows()
Dim LastRow As Long
Dim Cell As Range
Dim WS As Worksheet

For Each WS In Worksheets
With WS
.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).Value = _
Application.WorksheetFunction.SumIf( _
.Range("B:B"), "=" & Cell.Value, .Range("D"))
Next
End With
Next
End Sub

 

[Joel]

r: you don't want to exit the sub, you want to change the code to exit the
for loop.

 

[Rick Rothstein]

Joel makes a good argument in response to r's posting which applies to my
response as well. Change this line...

If Cell.Value = "" Then Exit Sub

to this...

If Cell.Value = "" Then Exit For

 

[r]

you are right ... I have not read carefully

regards
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/index.php/Excel-VBA/UsedRange-eccezioni-e-alternative.html

 

[Jeff Kelly]

Many thank to all.

Why did I not think of that ?

++++++++

 

[r]

you are right ... I have not read carefully

and I was in good company

regards
r

 

[Jeff Kelly]

Having now got totals on each sheet, how do I summate each item on a summary
sheet.

Go easy on me as at 72 years of age I'm only now just learning VBA

+++++++++