Sum data across multiple worksheets

[schulten]

Hello,

I'm trying to construct a macro that will add data from multiple worksheets, automatically accounting for any worksheets that may have been added since the last time the macro was run. The data will be in the same place on each sheet, though it would be nice if I could work in a VLOOKUP to account for sorting the data.

I assume I need to create a variable "n" that counts and run it in a loop until n=sheet.count or something similar, but I'm honestly brand new to macros and VB and don't have much of a handle on the vocabulary.

Any help is much appreciated.

Thanks.

 

[Derek Brown]

Hello and welcome to The Board.
You could consider 'spearing' as you will be referencing the same cell on each worksheet.
Have a look at MrExcel's Podcast #834. Unfortunately it is no longer available through this site but you will find it at:
http://www.youtube.com/watch?v=moMzsM-KwgQ

 

[schulten]

Thanks, that does help. Any idea how to create a macro that could update that formula to account for additional worksheets?

Thanks.

 

[AlphaFrog]

Here's some example code that will sum values from all worksheets excluding one worksheet named "Summary".

It sums two different values as an example on what you could do.

Sub Sum_3D()
    
    Dim ws As Worksheet, Tot1 As Variant, Tot2 As Variant
    
    For Each ws In Worksheets
        If ws.Name <> "Summary" Then
            Tot1 = Tot1 + Application.WorksheetFunction.Sum(ws.Range("A2:D2"))
            Tot2 = Tot2 + Application.WorksheetFunction.SumIf(ws.Range("A:A"), "Apples", ws.Range("B:B"))
        End If
    Next ws
    
    MsgBox "The total of A2:D2 from all sheets: " & Tot1
    MsgBox "The total of from column B in all sheets where Column A = Apple: " & Tot2
    
End Sub

 

[Dave Patton]

a non macro idea
Put a blank sheet named Start in front of the relevant data sheets and
a blank sheet named End after the relevant data sheets.

The formula could then be like the following

=Sum(Start:End!A4)

You would then insert any new sheets between the Start and End sheets.

 

[JuanPa]

Hi, Schulten. This in my suggestion:

[FONT=Courier New][SIZE=2][COLOR=Navy]Option [COLOR=Black][COLOR=Navy]Explicit[/COLOR]
[/COLOR][/COLOR][COLOR=Black][COLOR=Navy]Public Function [/COLOR]nSum([COLOR=Navy]Optional [/COLOR][/COLOR]Company_Filter [COLOR=Navy]As [/COLOR]String = "") [COLOR=Navy]As[/COLOR] Currency
    [COLOR=Navy]Dim [/COLOR]nSheet [COLOR=Navy]As [/COLOR]Worksheet
    [COLOR=DarkGreen]'Loops throught all the sheets in the current book...[/COLOR]
    [COLOR=Navy]For Each [/COLOR]nSheet [COLOR=Navy]In [/COLOR]ThisWorkbook.Worksheets
        [COLOR=DarkGreen]'...except the one where the formula is, in this case, Summary[/COLOR]
        [COLOR=Navy]If [/COLOR]nSheet.Name <> "Summary" [COLOR=Navy]Then[/COLOR]
            [COLOR=DarkGreen]'If no "Company" filter is requested, ...[/COLOR]
            [COLOR=Navy]If [/COLOR]Company_Filter = "" [COLOR=Navy]Then[/COLOR]
                [COLOR=DarkGreen]'...Applies function SUM to column B (amounts, in this case)...[/COLOR]
                nSum = nSum + WorksheetFunction.Sum(nSheet.Range("B1:B100"))
            [COLOR=Navy]Else[/COLOR]
                [COLOR=DarkGreen]'...Otherwise, applies the function SUMIF to column B, where _
                 [/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=DarkGreen]column A [/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=DarkGreen](company) is equal to the specified company[/COLOR]
                nSum = nSum + WorksheetFunction.SumIf(nSheet.Range("A1:A100"), _
                                                      Company_Filter, _
                                                      nSheet.Range("B1:B100"))
            [COLOR=Navy]End If[/COLOR]
        [COLOR=Navy]End If[/COLOR]
    [COLOR=Navy]Next [/COLOR]nSheet
[COLOR=Navy]End Function[/COLOR][/SIZE][/FONT]

Regards,

JuanPa

 

[schulten]

Thanks everyone!