VBA-总和为主工作表 [英] VBA - sum totals to a master worksheet

问题描述

好的，就这样.我有一本每天都有单独工作表的工作簿，详细介绍了股票交易活动.我目前还拥有一个VBA，可以为每个工作表上的指定列提供总计，并且效果很好.

Okay, so here goes. I have a workbook with individual worksheets for each day detailing the stock trading activity. I also currently have a VBA which provides a sum total for specified columns on each of these worksheets, and this works fine.

但是，我想做的是添加到我的VBA中，以便为主工作表中的这些列提供总计.

However, what I would like to do is add to my VBA so that it provides a sum total for these columns into the master worksheet.

因此，例如:如果工作表1上的交易活动在2018年10月1日总计为400万，而工作表2上的交易活动在2018年10月2日总计为300万，我希望将这700万的总交易量显示在主工作表上.工作表.

So, for example: If there was trading activity totalling 4m on the 1st Oct 2018 on worksheet 1, and trading activity totalling 3m on 2nd october 2018 on worksheet 2, I would like to have this total of 7m shown on the master worksheet.

我在下面附加了当前的vba，每个工作表上当前合计的列为J.单个工作表上合计的列不变，但是这些列中包含的数据量显然取决于交易活动.

I've attached my current vba below, the column currently being summed on each individual worksheet is J. The columns summed on the individual worksheet do not change, however the amount of data contained in those columns obviously does depending on trading activity.

Sub autoSum_AllSheets()
    Dim ws As Worksheet
    Dim cel1 As String, cel2 As String
    Dim firstCel As Range

    For Each ws In ActiveWorkbook.Worksheets
        With ws
            Set firstCel = .Range("J3").End(xlDown).Offset(2, 0)
            cel1 = firstCel.Offset(-2, 0).End(xlUp).Address
            cel2 = firstCel.Offset(-1).Address
            firstCel.Value = "=SUM(" & cel1 & ":" & cel2 & ")"
        End With
    Next ws
End Sub

我还附上了从随机一天中获取的当前模拟工作表的屏幕截图，运行vba后我得到的总和以粗体显示并用红色突出显示.

I've also attached a screenshot of a current mock worksheet taken from a random day, with the sum total i get after running the vba bolded and highlighted in red.

任何有关如何解决此问题的建议都非常有用，因为我是VBA所有事物的新手.

Any advice on how to approach this would be great as I'm a newcomer to all things VBA.

我在下面的主工作表上附有我要实现的模拟屏幕截图:

推荐答案

我推荐以下内容……

Option Explicit

Public Sub AutoSumAllWorkheets()
    Const MasterName As String = "Master" 'specify name of master sheet

    Dim wsMaster As Worksheet
    On Error Resume Next 'test if master exists
    Set wsMaster = ActiveWorkbook.Worksheets(MasterName)
    On Error GoTo 0

    If wsMaster Is Nothing Then 'add master if not exists
        Set wsMaster = ActiveWorkbook.Worksheets.Add(Before:=ActiveWorkbook.Worksheets(1))
        wsMaster.Name = MasterName
        'instead you can throw a message and exit here
        'MsgBox "No master found"
        'Exit Sub
    End If

    Dim FirstCell As Range, LastCell As Range

    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        With ws
            If .Name <> MasterName Then 'don't sum on master sheet
                Set FirstCell = .Range("J3")
                Set LastCell = FirstCell.End(xlDown)
                LastCell.Offset(2, 0).Formula = "=SUM(" & FirstCell.Address & ":" & LastCell.Address & ")"

                'write in master
                With wsMaster.Cells(wsMaster.Rows.Count, "A").End(xlUp)
                    .Offset(1, 0).Value = ws.Name
                    .Offset(1, 1).Formula = "=" & LastCell.Offset(2, 0).Address(External:=True)
                End With
            End If
        End With
    Next ws

    'sum all sheets up
    With wsMaster.Cells(wsMaster.Rows.Count, "A").End(xlUp)
        .Offset(2, 0).Value = "Total sum:"
        .Offset(2, 1).Formula = "=SUM(" & wsMaster.Cells(1, "B").Address & ":" & .Offset(0, 1).Address & ")"
    End With
End Sub

第一部分检查母版是否存在，如果不存在则添加一个.

The first part checks if a master sheet exists and adds one if it doesn't exist.

然后我对您的代码做了一些改进:

Then I improved your code a bit:

• 我建议使用清晰的变量名(使它更容易).例如，您的 firstCel 实际上不是第一个，而是求和单元格.这非常令人困惑，您很容易失败.
• 使用 .Formula 编写公式.
• 我添加了一些代码以将每张纸的总和写入主纸.请注意，这会将条目追加到主表中.因此，如果您运行两次，则需要首先清除母版表中的条目.

• I recommend to use clear variable names (makes it easier). For example your firstCel actually was not the first but the sum cell. That is very confusing and you will easily fail.
• Use .Formula to write a formula.
• I added some code to write the sums of each sheet into the master sheet. Note that this appends the entries at the master sheet. So if you run it twice you need to clear the entries in the master sheet first.

如果要写入主表的另一列，只需从更改 wsMaster.Cells(wsMaster.Rows.Count，"A").End(xlUp)的列名>"A" 到"L"

If you want to write into another column of the master sheet just change the column name of wsMaster.Cells(wsMaster.Rows.Count, "A").End(xlUp) from "A" to eg "L"

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