Insert formula in multiple excel sheets

Question

I am trying to insert formulas into an excel sheet with VBA but formula insert only one sheet

Sub RunSheets1()
Sheets("Sheet1").Activate
With ThisWorkSheet
   
          
                Range("B5").Formula = "=SUM(F3,G3,H3,I3,J3)"
End With

Sheets("Sheet2").Activate
With ThisWorkSheet
   
          
                Range("H6").Formula = "=SUM(F4,G4,H4,I4,J4)"
               Range("AC6").Formula = "=SUM(AC4,AD4,AE4,AF4,AG4)"
End With


Sheets("Sheet3").Activate
With ThisWorkSheet
   
          
                Range("H6").Formula = "=SUM(F4,G4,H4,I4,J4)"
               Range("AC6").Formula = "=SUM(AC4,AD4,AE4,AF4,AG4)"
End With

Sheets("Sheet4").Activate
With ThisWorkSheet
   
          
                Range("H6").Formula = "=SUM(F4,G4,H4,I4,J4)"
               Range("AC6").Formula = "=SUM(AC4,AD4,AE4,AF4,AG4)"
End With

Sheets("Sheet5").Activate
With ThisWorkSheet
   
          
                Range("H6").Formula = "=SUM(F4,G4,H4,I4,J4)"
               Range("AC6").Formula = "=SUM(AC4,AD4,AE4,AF4,AG4)"
End With




End Sub

score 5 · Answer 1 · answered Jan 16 '23 at 16:11

5

To fill across the sheets, you can use something like this:

With Sheets("Sheet2")
    .Range("H6").Formula = "=SUM(F4,G4,H4,I4,J4)"
    .Range("AC6").Formula = "=SUM(AC4,AD4,AE4,AF4,AG4)"
    Sheets(Array("Sheet2", "Sheet3", "Sheet4", "Sheet5")).FillAcrossSheets .Range("H6")
    Sheets(Array("Sheet2", "Sheet3", "Sheet4", "Sheet5")).FillAcrossSheets .Range("Ac6")
End With

answered Jan 16 '23 at 16:11

Rory

32,730
5
32
35

Fyi Related, but dealing with (header) *values* in *all* sheets instead of formulae in *some* sheets: [How to write identical information in a specific cell for all sheets](https://stackoverflow.com/questions/58879621/how-to-write-identical-information-in-a-specific-cell-for-all-sheets/58884112#58884112). This post was of some additional interest as it avoids - a needed - `.Select` demonstrated by BigBen's array solution for all sheets. @Rory – T.M. Jan 18 '23 at 19:28

score 0 · Answer 2 · answered Jan 16 '23 at 16:03

You should work with explicit referencing of worksheets and ranges:

Sub RunSheets1()

Dim ws As Worksheet

With ThisWorkbook

    With .Worksheets("Sheet1")
       .Range("B5").Formula = "=SUM(F3,G3,H3,I3,J3)"
    End With
    
    With .Worksheets("Sheet2")
       .Range("H6").Formula = "=SUM(F4,G4,H4,I4,J4)"
       .Range("AC6").Formula = "=SUM(AC4,AD4,AE4,AF4,AG4)"
    End With

End With

End Sub

Range("B5") is always implicitly referencing the active sheet. You should definitly avoid that!

Furthermore I recommend reading How to avoid using select.

Insert formula in multiple excel sheets

2 Answers2