Loop through worksheets

J

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

End Sub
 
J

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

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: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
 
R

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:D"))
Next
End With
Next
End Sub
 
J

Joel

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

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
 
J

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

+++++++++
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top