VBA to sum dynamically from all worksheets

wisewood

Board Regular
Joined
Nov 7, 2002
Messages
193
Can anyone please point me in the right direction for a method to perform the following action(s).

I have a workbook with several (and I mean A LOT) worksheets contained within it.
Each of these worksheets has an identical layout, but with different information contained within it (invoices).

What I am looking to do is to create a summary page that can reference each of these worksheets dynamically to sum the various amounts contained within them.
This needs to be done dynamically as I will be constantly adding new worksheets, and don’t want to have to update my summary page formulas every time I need to print an analysis for the data.

I’m assuming there will be a fairly straight forward VBA solution to this, but I don’t have a clue where to start.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Say your summary sheet is called "Summary". You can loop around the worksheets like this:

Code:
Dim WS As Worksheet
For Each WS in ThisWorkbook.Worksheets
   If WS.Name <> "Summary" Then
'      Code to gather your data
   End If
Next WS
 
Upvote 0

wisewood

Board Regular
Joined
Nov 7, 2002
Messages
193
OK, and if i want the code to gather my data to, for example, sum the values of cell D53 on each of those pages ??

Sorry, totally lame with VBA and a bit pushed for time on this one.
 
Upvote 0

WinteE

Well-known Member
Joined
Apr 8, 2007
Messages
605
The following code will help you getting started :

Code:
Sub SumData()

iC = Worksheets("Main").Cells(Cells.Rows.Count, "A").End(xlUp).Row

For Each C In Range("A2:A" & iC)
    C.Offset(0, 1).Value = 0
    C.Offset(0, 2).Value = -1
    For Each WS In Worksheets
        iWS = WS.Cells(Cells.Rows.Count, "A").End(xlUp).Row
        For Each D In WS.Range("A1:A" & iWS)
            If UCase(C.Value) = UCase(D.Value) Then
                C.Offset(0, 1).Value = C.Offset(0, 1).Value + D.Offset(0, 1).Value
                C.Offset(0, 2).Value = C.Offset(0, 2).Value + 1
            End If
        Next D
    Next WS
Next C

End Sub

Private Sub CommandButton1_Click()

Collect
SumData

End Sub
Sub Collect()

Dim Counter As Long
Dim CheckCounter As Long
Counter = 2
CheckCounter = 0

iDataToSum = Worksheets("Main").Cells(Cells.Rows.Count, "A").End(xlUp).Row
Worksheets("Main").Range("A2:C" & iDataToSum).ClearContents

For Each WorkSh In Worksheets
    iWorkSh = WorkSh.Cells(Cells.Rows.Count, "A").End(xlUp).Row
    For Each E In WorkSh.Range("A1:A" & iWorkSh)
        For Each F In Range("A1:A" & iDataToSum)
            If UCase(F.Value) = UCase(E.Value) Then
                CheckCounter = CheckCounter + 1
            ElseIf UCase(E.Value) = Null And UCase(F.Value) = Null Then
                CheckCounter = CheckCounter + 1
            End If
        Next F
        If CheckCounter = 0 Then
            Worksheets("Main").Cells(Counter, 1).Value = E.Value
            Counter = Counter + 1
        End If
        CheckCounter = 0
        iDataToSum = Worksheets("Main").Cells(Cells.Rows.Count, "A").End(xlUp).Row
    Next E
Next WorkSh

End Sub

The Sub "SumData" will summarize the items in the first sheet "Main". The Sub "Collect" I've made to collect all unique items from all worksheets into sheet "Main".

Erik
 
Upvote 0

Krishnakumar

Well-known Member
Joined
Feb 28, 2003
Messages
2,615
Hi,

Insert two worksheets before and after your data sheets and name them First and Last respectively(just leave these two sheets empty).

Now on your summary sheet

=SUM(First:Last!D53)

HTH
 
Upvote 0

Forum statistics

Threads
1,190,789
Messages
5,982,924
Members
439,807
Latest member
WXM86

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top