Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Loop through worksheets
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6
Default 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


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default 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



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
r r is offline
external usenet poster
  
Posts: 125
Default 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



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,934
Default 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


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,934
Default 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


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default 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



Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,934
Default 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



Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
r r is offline
external usenet poster
  
Posts: 125
Default 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



Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
r r is offline
external usenet poster
  
Posts: 125
Default 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



Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6
Default 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



Reply With QuoteReply With Quote
  #11   Report Post  
Posted to microsoft.public.excel.programming
r r is offline
external usenet poster
  
Posts: 125
Default Loop through worksheets
"r" wrote:

you are right ... I have not read carefully


and I was in good company :-)

regards
r
Reply With QuoteReply With Quote
  #12   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6
Default 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



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Loop through worksheets [email protected] Excel Programming 3 June 28th 08 11:16 AM
Naming Worksheets - Loop within a loop issue klysell Excel Programming 5 March 29th 07 05:48 AM
Naming Worksheets - Loop within a loop issue klysell Excel Programming 0 March 27th 07 11:17 PM
(Complex) Loop within loop to create worksheets klysell Excel Programming 1 March 20th 07 12:03 AM
Loop worksheets Steve[_35_] Excel Programming 3 November 25th 03 04:46 AM


All times are GMT +1. The time now is 04:05 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement