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
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