Loop through worksheets

  • Thread starter Thread starter Jeff Kelly
  • Start date Start date
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
 
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
 
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
 
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
 
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
 
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

+++++++++
 
Back
Top