ExcelBanter - Loop through worksheets

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Loop through worksheets (https://www.excelbanter.com/excel-programming/429377-loop-through-worksheets.html)

Jeff Kelly

Loop through worksheets

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

joel

Loop through worksheets

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

Loop through worksheets

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/...ternative.html

"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

Rick Rothstein

Loop through worksheets

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

--
Rick (MVP - Excel)

"Jeff Kelly" wrote in message
...
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

Rick Rothstein

Loop through worksheets

joel

Loop through worksheets

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

"r" wrote:

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/...ternative.html

"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

Rick Rothstein

Loop through worksheets

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

--
Rick (MVP - Excel)

"Rick Rothstein" wrote in message
...
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

--
Rick (MVP - Excel)

"Jeff Kelly" wrote in message
...
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

Loop through worksheets

you are right ... I have not read carefully

regards
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/...ternative.html

"Joel" wrote:

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

"r" wrote:

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/...ternative.html

"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

Loop through worksheets

Jeff Kelly

Loop through worksheets

Many thank to all.

Why did I not think of that ?

++++++++
"Jeff Kelly" wrote in message
...
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

Loop through worksheets

"r" wrote:

you are right ... I have not read carefully

and I was in good company :-)

regards
r

Jeff Kelly

Loop through worksheets

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

+++++++++
"Jeff Kelly" wrote in message
...
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

All times are GMT +1. The time now is 03:48 PM.