PDA

View Full Version : Solved: Loop through sheets



Meatball
03-09-2009, 01:17 PM
I need this code altered to loop through all sheets after Sheet 2, the quantity of sheets will be ever changing. If any cleaning of the code can be done , that would be fine with me. I would also like to send each sheet to a recipient whose e-mail address would be in cell A1 of each sheet.


Sub SortForZeroFirst()'' SortForZeroFirst Macro' Macro recorded 3/9/2009 by David D'' Sheets(3).Activate Range("A5:I" _
& Range("B2000").End(xlUp).Row).Select Selection.Sort Key1:=Range("I5"), Order1:=xlAscending, Key2:=Range("C5") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2 _ :=xlSortNormal Range("A1").Select Sheets(4).Activate Range("A5:I" & _
Range("B2000").End(xlUp).Row).Select Selection.Sort Key1:=Range("I5"), Order1:=xlAscending, Key2:=Range("C5") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2 _ :=xlSortNormal Range("A1").Select
End Sub


Thanks in advance for any help.

Meatball
03-09-2009, 01:19 PM
Trying to make the code read better


Sub SortForZeroFirst()
'
' SortForZeroFirst Macro
' Macro recorded 3/9/2009 by David D
'
'

Sheets(3).Activate
Range("A5:I" & Range("B2000").End(xlUp).Row).Select

Selection.Sort Key1:=Range("I5"), Order1:=xlAscending, Key2:=Range("C5") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
Range("A1").Select

Sheets(4).Activate
Range("A5:I" & Range("B2000").End(xlUp).Row).Select

Selection.Sort Key1:=Range("I5"), Order1:=xlAscending, Key2:=Range("C5") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
Range("A1").Select
End Sub

mdmackillop
03-09-2009, 01:43 PM
Try
Sub SortForZeroFirst()
'
' SortForZeroFirst Macro
' Macro recorded 3/9/2009 by David D
'
'

For i = 3 To Sheets.Count
With Sheets(i)
rw = .Cells(Rows.Count, 2).End(xlUp).Row
.Range("A5:I" & rw).Sort Key1:=.Range("I5"), Order1:=xlAscending, Key2:=.Range("C5") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
End With
Next
End Sub

Bob Phillips
03-09-2009, 01:43 PM
Sub SortForZeroFirst()
'
' SortForZeroFirst Macro
' Macro recorded 3/9/2009 by David D
'
'

For i = 3 To Worksheets.Count

With Worksheets(i)

.Range ("A5:I" & Range("B2000").End(xlUp).Row)

.Sort Key1:=.Range("I5"), Order1:=xlAscending, _
Key2:=Range("C5"), Order2:=xlAscending, _
Header:=xlGuess
End With
Next i
End Sub

Meatball
03-09-2009, 01:54 PM
Thank you very much gentlemen.
xld, your code causes a compile error, "End With without With"
mdm, works Great. Thanks

Bob Phillips
03-09-2009, 02:37 PM
That is because the second End With should read Next i

Meatball
03-10-2009, 05:34 AM
xld,
This produced another error, this time at the Range.