how  to create Totals for the same column for all sheets in an Excel workbook. VBA

donohara1
donohara1 used Ask the Experts™
on
I have Excel workbooks that have a variable number of sheets. I would like to sum the same column in each sheet, and create a table (in a new sheet) that has the sheet name, total and number of values in 3 columns. There is no other numeric data in these columns
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
If you are using Excel 2007 or 2010, I would simple create Tables for your various worksheets, and then in Table Tools/Design ribbon tab, select Total Row in the Table Style Options to force a grand total row.

Then, on another worksheet, use formulas to pull in the grand totals, such as:

=SUM(Table1[MyValueColumnName])
=SUM(Table2[MyValueColumnName])
=SUM(Table3[MyValueColumnName])
etc.

If you have the name of the table in, say, Col A, you can use:

=SUM(INDIRECT(A1&"[MyValueColumnName]"))
Commented:
Another option is to use a macro. Take a look at the attached worksheet and run the macro "TallyColumns" SheetTally.xlsm

It will produce a sheet called "Summary" that looks like this:
 Summary sheet example
The top part of the macro will let you select which columns to look at. Right now it's set to "C","D","E", but you can change it to whatever you'd like. The macro is as follows:
 
Sub TallyColumns()
    Dim columnList() As Variant
    Dim columnCount() As Variant
    Dim columnSum() As Variant
    Dim sheetNames() As Variant
    Dim newSheet As Worksheet
    
    ' List of columns to sum/count
    columnList = Array("C", "D", "E")
    
    ReDim columnCount(Sheets.Count - 1, UBound(columnList))
    ReDim columnSum(Sheets.Count - 1, UBound(columnList))
    ReDim sheetNames(Sheets.Count - 1)
    
    For i = 1 To Sheets.Count
        sheetNames(i - 1) = Sheets(i).Name
        For j = 0 To UBound(columnList)
            columnCount(i - 1, j) = Application.WorksheetFunction.Count(Sheets(i).Columns(columnList(j)))
            columnSum(i - 1, j) = Application.WorksheetFunction.Sum(Sheets(i).Columns(columnList(j)))
        Next j
    Next i
    
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Summary"
    For i = 0 To UBound(columnList)
        With Range(Cells(1, i * 2 + 2), Cells(1, i * 2 + 3))
            .Merge
            .Value = "Column " & columnList(i)
            .HorizontalAlignment = xlCenter
            .Font.Bold = True
        End With
        With Cells(2, i * 2 + 2)
            .Value = "Count"
            .HorizontalAlignment = xlCenter
            .Font.Bold = True
        End With
        With Cells(2, i * 2 + 3)
            .Value = "Sum"
            .HorizontalAlignment = xlCenter
            .Font.Bold = True
        End With
    Next i
    With Cells(2, 1)
        .Value = "Sheet Name"
        .HorizontalAlignment = xlCenter
        .Font.Bold = True
    End With
    For i = 0 To UBound(sheetNames)
        With Cells(i + 3, 1)
            .Value = sheetNames(i)
            .HorizontalAlignment = xlRight
            .Font.Bold = True
        End With
        For j = 0 To UBound(columnList)
            Cells(i + 3, j * 2 + 2) = columnCount(i, j)
            Cells(i + 3, j * 2 + 3) = columnSum(i, j)
        Next j
    Next i
    Columns("A").EntireColumn.AutoFit
End Sub

Open in new window

Author

Commented:
Perfect.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial