2

I want to repeat this code on all the worksheets in a workbook.

There may sometimes be 1-2 worksheets sometimes 50+.

Sub HoursTotal()
'
' HoursTotal Macro
'

Range("F2").Select
ActiveCell.FormulaR1C1 = "=SUM(C[-1])"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Total Hours"
Range("G1").Select


End Sub
Community
  • 1
  • 1
Alex Ross
  • 39
  • 1
  • 1
  • 6
  • Welcome to SO! To help with your code as well, try to avoid using `.Select`. It will make your code run a bit slower. You can find more information about it from [This Question](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). – PartyHatPanda Sep 19 '16 at 20:34

3 Answers3

6

This should do it.

Sub HoursTotal()
    Dim ws As Worksheet

    For Each ws In Worksheets
        ws.Range("F2").FormulaR1C1 = "=SUM(C[-1])"
        ws.Range("F1").FormulaR1C1 = "Total Hours"
        ws.Range("G1").Select 'I don't think you need this line but I included it anyways
    Next
End Sub
BerticusMaximus
  • 705
  • 5
  • 16
1

Simple modification of your current code should do it:

Sub HoursTotal()
'
' HoursTotal Macro
'

Dim ws as Worksheet

For Each ws in Worksheets

    ws.Range("F2").Select
    ActiveCell.FormulaR1C1 = "=SUM(C[-1])"
    ws.Range("F1").Select
    ActiveCell.FormulaR1C1 = "Total Hours"
    ws.Range("G1").Select

Next ws

End Sub

But here's what it looks like without the Select's

Sub HoursTotal()
'
' HoursTotal Macro
'

Dim ws as Worksheet

For Each ws in Worksheets

    ws.Range("F2").FormulaR1C1 = "=SUM(C[-1])"
    ws.Range("F1").FormulaR1C1 = "Total Hours"
    ws.Range("G1").Select

Next ws

End Sub
PartyHatPanda
  • 712
  • 8
  • 14
  • Haha I figured because it was included in the original code without any action taken upon it, the user wanted the ending active cell to be G1 – PartyHatPanda Sep 19 '16 at 21:45
  • 1
    That's what I thought but ...ooo..yuck! they're soo gross!! +1 for thinking about the OP's point of view, –  Sep 19 '16 at 21:49
0

You need to activate the worksheet so that excel can make changes to it.

`Sub HoursTotal() Dim ws As Worksheet

For Each ws In Worksheets
    ws.Activate
    ws.Range("F2").FormulaR1C1 = "=SUM(C[-1])"
    ws.Range("F1").FormulaR1C1 = "Total Hours"
    ws.Range("G1").Select 'I don't think you need this line but I included it anyways
Next

End Sub`

abe973t
  • 117
  • 2
  • 6