在多个Excel工作表中插入公式

xpcnnkqh 于 2023-01-18 发布在其他

关注(0)|答案(2)|浏览(201)

我尝试使用VBA将公式插入Excel工作表，但公式只能插入一个工作表

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

excel

来源：https://stackoverflow.com/questions/75136715/insert-formula-in-multiple-excel-sheets

2条答案

按热度按时间

uyto3xhc1#

要在工作表中填充，可以使用类似以下的代码：

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

赞(0）回复(0）举报 2023-01-18

tzdcorbm2#

您应该使用工作表和范围的显式引用：

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")总是隐式引用 * 活动工作表 *。您应该明确避免这种情况！
此外，我建议阅读How to avoid using select。

赞(0）回复(0）举报 2023-01-18

我来回答

在多个Excel工作表中插入公式

2条答案

相关问题

热门标签

最新问答