vba 自动汇总总表中新添加的 Excel 表

Automatically sum of new added Excel Sheet in Total Sheet

提问by user1100199

I have an Excel workbook in which I have tabs representing dates along with sum in each tab. Although I can take the sum of all these in the final sheet, I want a formula/macro to get the sum in the total named sheet, when a new spreadsheet is being added.

我有一个 Excel 工作簿，其中有表示日期的选项卡以及每个选项卡中的总和。虽然我可以在最终工作表中计算所有这些的总和，但当添加新电子表格时，我想要一个公式/宏来获取总命名工作表中的总和。

Note:- the cell in all would remain the same (E56)

注意：-所有单元格将保持不变（E56）

采纳答案by Tony Dallimore

I do not understand what you are attempting. Until the user has placed information in the new sheet that results in a value in E56, I see little point to adding the value of NewSheet!E56 to the total sheet.

我不明白你在尝试什么。在用户在新工作表中放置导致 E56 中的值的信息之前，我认为将 NewSheet!E56 的值添加到总工作表几乎没有意义。

However I suspect you need to use events. Below are a number of event routines which must be placed in the Microsoft Excel Object ThisWorkbookfor the workbook. These just output to the Immediate window so you can see when they are fired. Note: several can be fired for one user event. For example, creating a new worksheet, triggers: "Create for new sheet", "Deactivate for old sheet" and "Activate for new sheet".

但是我怀疑您需要使用事件。下面是一些必须放置在ThisWorkbook工作簿的 Microsoft Excel 对象中的事件例程。这些只是输出到立即窗口，以便您可以看到它们何时被触发。注意：可以为一个用户事件触发多个。例如，创建一个新工作表，触发：“为新工作表创建”、“为旧工作表停用”和“为新工作表激活”。

Do not forget to include

不要忘记包括

    Application.EnableEvents = False

    Application.EnableEvents = True

around any statement within one of these routine that will trigger an event.

围绕将触发事件的这些例程之一中的任何语句。

Perhaps you need to use SheetDeactivate. When the users leaves a sheet, check for a value in E56. If present, check for its inclusion in the totals sheet. Have a play. Do what your users do. Add to these routines to investigate further. Good luck.

也许您需要使用 SheetDeactivate。当用户离开工作表时，检查 E56 中的值。如果存在，请检查其是否包含在总计表中。有戏。做你的用户所做的。添加到这些例程以进一步调查。祝你好运。

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

  Debug.Print "Workbook_SheetActivate " & Sh.Name

End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)

   Call MsgBox("Workbook_BeforeClose", vbOKOnly)

End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range)

  Debug.Print "Workbook_SheetChange " & Sh.Name & " " & Source.Address

End Sub
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)

  Debug.Print "Workbook_SheetDeactivate " & Sh.Name

End Sub
Private Sub Workbook_NewSheet(ByVal Sh As Object)

  Debug.Print "Workbook_NewSheet " & Sh.Name

End Sub
Sub Workbook_Open()

   Debug.Print "Workbook_Open"

End Sub

Extra section in response to clarification of requirement

响应澄清要求的额外部分

The code below recalculates the grand total of cell E56 for all worksheets except TOTAL and stores the result in worksheet TOTAL every time the workbook is opened and every time the user changes the current worksheet.

下面的代码为除 TOTAL 之外的所有工作表重新计算单元格 E56 的总计，并在每次打开工作簿和每次用户更改当前工作表时将结果存储在工作表 TOTAL 中。

It is difficult to get consistent timings with Excel but according to my experimentation you would need between 500 and 1,000 worksheets before the user would notice a delay switching worksheets because of this recalculation.

使用 Excel 很难获得一致的计时，但根据我的实验，您需要 500 到 1,000 个工作表，然后用户才会注意到由于这种重新计算而延迟切换工作表。

I am not sure if you know how to install this code so here are brief instructions. Ask if they are too brief.

我不确定您是否知道如何安装此代码，因此这里是简要说明。问他们是否太简短了。

• Open the relevant workbook.
• Click Alt+F11. The VBA editor displays. Down the left you should see the Project Explorer. Click Ctrl+Rif you do not. The Project Explorer display will look something like:

• 打开相关工作簿。
• 单击Alt+F11。显示 VBA 编辑器。在左侧，您应该会看到 Project Explorer。Ctrl+R如果没有，请单击。Project Explorer 显示将类似于：

.

.

VBAProject (Xxxxxxxx.xls)
    Microsoft Excel Objects
       Sheet1 (Xxxxxxxxx)
       Sheet10 (Xxxxxxxxx)
       Sheet11 (Xxxxxxx)
       :
       ThisWorkbook

• Click ThisWorkbook. The top right of the screen with turn white.
• Copy the code below into that white area.
• No further action is required. The macros Workbook_Open() and Workbook_SheetDeactivate() execute automatically when appropriate.

• 单击ThisWorkbook。屏幕右上方变白。
• 将下面的代码复制到那个白色区域。
• 不需要采取进一步行动。宏 Workbook_Open() 和 Workbook_SheetDeactivate() 在适当时自动执行。

Good luck.

祝你好运。

Option Explicit

Sub CalcAndSaveGrandTotal()

  Dim InxWksht As Long
  Dim TotalGrand As Double

  TotalGrand = 0#
  For InxWksht = 1 To Worksheets.Count
    If Not UCase(Worksheets(InxWksht).Name) = "TOTAL" Then
      ' This worksheet is not the totals worksheet
      If IsNumeric(Worksheets(InxWksht).Range("E56").Value) Then            '###
        TotalGrand = TotalGrand + Worksheets(InxWksht).Range("E56").Value
      End If                                                                '###
    End If
  Next

  'Write grand total to worksheet TOTAL
  ' ##### Change the address of the destination cell as required  
  Worksheets("TOTAL").Range("D6").Value = TotalGrand

End Sub
Sub Workbook_Open()

  ' The workbook has just been opened.

   Call CalcAndSaveGrandTotal

End Sub
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)

  ' The user has selected a new worksheet or has created a new worksheet.

   Call CalcAndSaveGrandTotal

End Sub

回答by Jerry Beaucaire

I know this is the programming forum, but this particular "need" seems to be solvable without all the plumbing.

我知道这是编程论坛，但这个特殊的“需求”似乎可以在没有所有管道的情况下解决。

I like the old hidden FIRST and LAST sheets trick.

我喜欢旧的隐藏的第一张和最后一张床单技巧。

1. Create a sheet called First
2. Create a sheet called Last
3. Place your current data sheets between these two sheets.
4. Hide the sheets Firstand Last
5. Now you can use 3D formulas to sum cells from all these sheets, like so: =SUM(First:Last!E56)
6. Now just add sheets to your workbook AFTER the last visible data sheet and Excel will still slip it in ahead of the hidden LAST sheet, so your formula just expands itself that way

1. 创建一个名为First的工作表
2. 创建一个名为Last的工作表
3. 将您当前的数据表放在这两个表之间。
4. 隐藏工作表First和Last
5. 现在您可以使用 3D 公式对所有这些工作表中的单元格求和，如下所示： =SUM(First:Last!E56)
6. 现在只需在最后一个可见数据表之后将工作表添加到您的工作簿中，Excel 仍会将其滑入隐藏的最后一个工作表之前，因此您的公式只会以这种方式扩展自身