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
CodePudding user response:
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
CodePudding user response:
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.