动态工作表总数 [英] Sum across dynamic number of sheets

问题描述

我有一个宏，可以创建一个包含多个以日期命名的工作表的工作簿，例如2015年12月1日，2015年12月2日.将数据手动输入到那些工作表中.最后一个工作表名称为"TOTAL".每个月工作表的数量及其名称将有所不同.我正在尝试编写一段代码，将所有工作表中的数据汇总到位于工作表中名为总计"的单元格中.我的想法是通过循环执行此操作，但是尽管在运行宏时未显示任何错误消息，但它无法正常工作. 宏完成运行后，总计"工作表的"B2"中应该没有存在的公式，因此我认为求和仅在执行宏的时间内起作用.我不确定如何在总计"工作表的"B2"单元格中输入求和"公式.我会非常感激任何指向正确方向的提示.下面有一段不起作用的代码:

I have a macro that creates a workbook with multiple worksheets named by dates e.g. 12-01-2015, 12-02-2015. Data is entered manually into those worksheets. The last worksheet name is "TOTAL". Each month the number of worksheets and their names will be different. I am trying to write a piece of code that will sum the data from all sheets into cells located in the worksheet called "Total". My idea was to do it with looping but it does not work despite there are no error messages displayed while macro is running. The formula that should be present in "B2" of "Total" worksheet after macro finishes running is not there so my assumption is that summing works only during the time when macro is being executed. I am not sure how to enter "sum" formula into "B2" cell of "Total" worksheet. I would really appreciate any tip that would point me in the right direction. Below there is a piece of code that is not working:

Sub Test()

Dim i as Long

For i = 1 To Worksheets.Count - 1

             Worksheets("TOTAL").Range("B2").Formula = Application.WorksheetFunction.Sum(Worksheets(i).Range("B2"))

       Next i

       Worksheets(i).Range("B2").Value = Worksheets("TOTAL").Range("B2").Value

End Sub

推荐答案

您可以定义一个自定义的用户定义函数(UDF)，当在单元格中输入该函数时，它会汇总同一工作簿中所有其他工作表的相应单元格.让我们称之为AutoSum:

You can define a custom User-Defined Function (UDF) that when entered in a cell, sums-up the corresponding cells from all other worksheets within the same workbook. Let us call it AutoSum:

Function AutoSum() As Variant
    AutoSum = 0
    For Each ws In Worksheets
        If Not ws Is Application.ThisCell.Parent Then AutoSum = AutoSum + ws.Range(Application.ThisCell.Address)
    Next
End Function

然后，您可以在任何单元格的公式中简单地使用它:=AutoSum()

Then you can simply use it in formula in any cell: =AutoSum()

这篇关于动态工作表总数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！