Can anyone please correct this VBA loop

Human_doing

Board Regular
Joined
Feb 16, 2011
Messages
137
Hi all,

I'm trying to put a loop together that cycles through each worksheet in a workbook and performs an =sum(first cell:last cell) three times for three different years, however the loop doesn't work, it only performs the task for the first worksheet, could anyone please assist?

Thanks

Code:
Sub Totaler()
'Add year totals in cells K14, K27 & K40
Dim i As Long
 
Application.ScreenUpdating = False
 
For i = 1 To Worksheets.Count
    With Sheets(i)
    .Range("K14").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-12]C:R[-1]C)"
    .Range("K27").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-12]C:R[-1]C)"
    .Range("K40").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-12]C:R[-1]C)"
    End With
 
Next i
 
Sheets(1).Select
 
Application.ScreenUpdating = False
End Sub
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
You can only select a cell on the active sheet. But selecting isn't necessary so try:

Code:
Sub Totaler()
'   Add year totals in cells K14, K27 & K40
    Dim i As Long
    Application.ScreenUpdating = False
    For i = 1 To Worksheets.Count
        With Sheets(i)
            .Range("K14").FormulaR1C1 = "=SUM(R[-12]C:R[-1]C)"
            .Range("K27").FormulaR1C1 = "=SUM(R[-12]C:R[-1]C)"
            .Range("K40").FormulaR1C1 = "=SUM(R[-12]C:R[-1]C)"
        End With
    Next i
    Application.ScreenUpdating = False
End Sub
 
Upvote 0
Code:
ub Totaler()
    
    'Add year totals in cells K14, K27 & K40
    Dim i As Long
     
    Application.ScreenUpdating = False
     
    For i = 1 To Worksheets.Count
        With Sheets(i)
        .Range("K14").FormulaR1C1 = "=SUM(R[-12]C:R[-1]C)"
        .Range("K27").FormulaR1C1 = "=SUM(R[-12]C:R[-1]C)"
        .Range("K40").FormulaR1C1 = "=SUM(R[-12]C:R[-1]C)"
        End With
     
    Next i
 
    Sheets(1).Select
 
    Application.ScreenUpdating = False
    
End Sub
 
Upvote 0
You should either activate the sheet(i) before trying to select ranges from it or simply skip the selecting part:
Code:
    With Sheets(i)
        .Range("K14").FormulaR1C1 = "=SUM(R[-12]C:R[-1]C)"
        .Range("K27").FormulaR1C1 = "=SUM(R[-12]C:R[-1]C)"
        .Range("K40").FormulaR1C1 = "=SUM(R[-12]C:R[-1]C)"
    End With
 
Upvote 0

Forum statistics

Threads
1,224,520
Messages
6,179,266
Members
452,902
Latest member
Knuddeluff

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top