PDA

View Full Version : Solved: Looping through wooksheets



schroederd1
11-05-2011, 07:44 PM
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.

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

mikerickson
11-05-2011, 09:46 PM
All that selecting was not needed.
Qualifying ranges makes selection of sheets unnesseary and allows for faster looping.

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

Kenneth Hobs
11-05-2011, 09:49 PM
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.

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

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