Solved: Loop through sheets

[Meatball]

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.


[VBA]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[/VBA]


Thanks in advance for any help.

[Meatball]

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]

Try
[VBA]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
[/VBA]

[Bob Phillips]

[vba]

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
[/vba]

[Meatball]

Thank you very much gentlemen.
xld, your code causes a compile error, "End With without With"
mdm, works Great. Thanks

[Bob Phillips]

That is because the second End With should read Next i

[Meatball]

xld,
This produced another error, this time at the Range.