Solved: Looping through wooksheets

[schroederd1]

I need to loop through Sheets 10 thru 44. I was starting with this code but it won't move on to the next sheet it just stays on the same sheet. Please help. Thanks.

[VBA]Sheets("Bailey").Select
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Range("F8").Select
Selection.End(xlDown).Select
ActiveCell.Offset(2, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=subtotal(9,(offset(R8C,0,0,counta(R8C1:R900C1)-0,1)))"
ActiveCell.Select
Selection.Copy
ActiveCell.Offset(0, 1).Range("A1:F1").Select
ActiveSheet.Paste
Range("M8").Select
Selection.End(xlDown).Select
ActiveCell.Offset(2, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=RC[-3]/RC[-1]"
Range("p8").Select
Selection.End(xlDown).Select
ActiveCell.Offset(2, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=subtotal(9,(offset(R8C,0,0,counta(R8C1:R900C1)-0,1)))"
ActiveCell.Select
Selection.Copy
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
Range("R8").Select
Selection.End(xlDown).Select
ActiveCell.Offset(2, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=(RC[-1]/RC[-8])"
Selection.Style = "Percent"
Selection.NumberFormat = "0.00%"
Columns("D:R").Select
Columns("D:R").EntireColumn.AutoFit
Columns("F:K").Select
Selection.Style = "comma"
Columns("M:M").Select
Selection.Style = "comma"
Columns("P:Q").Select
Selection.Style = "comma"
Next ws

End Sub[/VBA]

[mikerickson]

All that selecting was not needed.
Qualifying ranges makes selection of sheets unnesseary and allows for faster looping.

[VBA]Sub test()
Dim ws As Worksheet
'Sheets("Bailey").Select

For Each ws In ActiveWorkbook.Worksheets
With ws
With .Range("F8").End(xlDown).Offset(2, 0)
.FormulaR1C1 = "=subtotal(9,(offset(R8C,0,0,counta(R8C1:R900C1)-0,1)))"
.Copy Destination:=.Offset(0, 1).Range("A1:F1")
Application.CutCopyMode = False
End With
.Range("M8").End(xlDown).Offset(2, 0).FormulaR1C1 = "=RC[-3]/RC[-1]"
With .Range("p8").End(xlDown).Offset(2, 0)
.FormulaR1C1 = "=subtotal(9,(offset(R8C,0,0,counta(R8C1:R900C1)-0,1)))"
.Copy Destination:=.Offset(0, 1)
Application.CutCopyMode = False
End With
With .Range("R8").End(xlDown).Offset(2, 0)
.FormulaR1C1 = "=(RC[-1]/RC[-8])"
.Style = "Percent"
.NumberFormat = "0.00%"
End With
.Columns("D:R").EntireColumn.AutoFit
.Columns("F:K").Style = "comma"
.Columns("M:M").Style = "comma"
.Columns("P:Q").Style = "comma"
End With
Next ws

End Sub[/VBA]

[Kenneth Hobs]

Please use vba code tags.

You have several issues going on. Without doing it all for you, here is a start to show the concepts.

[vba]Sub test()
Dim ws As Worksheet, i As Integer
On Error Resume Next
For i = 10 To 44
Set ws = Worksheets(i)
ws.Select
Range("F8").End(xlDown).Offset(2, 0).FormulaR1C1 = _
"=R1C1+1"
Next i
End Sub[/vba]

Mike is too fast: Guess I am all shook up after the aftershock here in Oklahoma...