多个工作表中的Excel VBA总和 [英] Excel VBA Sum from Multiple Sheets

问题描述

我正在尝试创建一个或多个功能，可以对每个客户的时间卡中的小时进行总计，以得出每天的总工作时间.每个客户在单个工作簿中都有自己的工作表.

I am trying to create a function or functions that can sum daily hours from time cards for each client to come up with the total hours worked per day. Each client has it's own sheet inside of a single workbook.

当前，我有一个函数来确定与第一个客户端一起使用的工作表(工作簿中的第三个工作表):

Currently, I have a function that determines the sheet that goes with the first client (the third sheet in the workbook):

Function FirstSheet()
Application.Volatile
FirstSheet = Sheets(3).Name
End Function

找到最后一张纸:

Function LastSheet()
Application.Volatile
LastSheet = Sheets(Sheets.Count).Name
End Function

我遇到麻烦的部分使它们无法在sum函数中工作.

The part that I am having trouble with it getting these to work within the sum function.

=sum(FirstSheet():LastSheet()!A1

那基本上就是我想要完成的.我认为问题在于，我不知道如何在不将其转换为字符串的情况下进行连接，并且没有意识到它是工作表和单元格引用.

That is basically what I want to accomplish. I think the problem is that I don't know how to concatenate it without turning it into a string and it doesn't realize that it is sheet and cell references.

任何帮助将不胜感激.

推荐答案

因此，示例公式如下所示:

So, an example formula would look like this:

= SUM(Sheet2！A1:A5，Sheet3！A1:A5，Sheet4！A1:A5)

这将在所有工作表上累加 Sheet2-Sheet4 和 A1:A5 .

That would sum Sheet2-Sheet4, A1:A5 on all sheets.

您是否有理由编写VBA代码来做到这一点?

Is there a reason you need to write the VBA code to do this?

您不能一次将其作为公式输入吗?

Can't you just enter it as a formula once?

此外，如果您要编写VBA来生成公式的麻烦，则完全在VBA代码中进行总和可能更有意义.

Also, if you're going to the trouble of writing VBA to generate a formula, it may make more sense to just do the sum entirely in VBA code.

如果没有，请尝试以下操作:

If not, try this:

Sub GenerateTheFormula()
Dim x, Formula
Formula = "=SUM(" 'Formula begins with =SUM(
For x = 3 To Sheets.Count
    Formula = Formula & Sheets(x).Name & "!A1," 'Add SheetName and Cell and Comma
Next x
Formula = Left(Formula, Len(Formula) - 1) & ")" 'Remove trailing comma and add parenthesis
Range("B1").Formula = Formula 'Where do you want to put this formula?
End Sub

结果:

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