summarizing data from multiple tabs in Excel

H

Harry_1234

New Member
Joined
Aug 19, 2017
Messages
47
I have 28 tabs of data (named AB, BC, CD, EF, FG, GH, HI, IJ, JK, KL, LM, MN, NO, OP, PQ, QR, RS, ST, TU, UV, VW, WX, XY, YZ, Z00, Z01, Z02, Z03) in my spread-sheet with varying data (columns and rows are inconsistent) in each tab. I need a summary tab with count of each column per tab(only should take columns with values into consideration, ignore empty columns) and should be dynamic (i.e. if someone adds an entry into column, the summary tab should update or could click on update to reflect new count). What would be the best way to accomplish this barring pivot tables as i need the flexibility of real-time update. Attached is what i am trying to accomplish?
 

Attachments

  • AB.PNG
    AB.PNG
    26.5 KB · Views: 33
  • BC.PNG
    BC.PNG
    35.2 KB · Views: 33
  • Summary.PNG
    Summary.PNG
    20.7 KB · Views: 32

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
Place this code in a NEW module
Ensure that sheet "Summary" already exists
Hopefully code is all fairly self explanatory

VBA Code: 
Sub Summarise()
    Dim ItemCount As Long, c As Long, ws As Worksheet
    Sheets("Summary").Cells.Clear
   
    For Each ws In ThisWorkbook.Worksheets
        Select Case ws.Name
            Case "Summary", "SheetX", "SheetY"
                'do nothing with excluded sheets
            Case Else
                If SheetHasValues(ws) Then
                    Call InsertValues("Site:", ws.Name)                                             'Insert SiteHeader
                    For c = 1 To ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
                        ItemCount = WorksheetFunction.CountA(ws.Columns(c)) - 1
                        If ItemCount > 0 Then Call InsertValues(ws.Cells(1, c), ItemCount)          'Insert count
                    Next c
                    Call InsertValues("Total DIDs:", "count what ????")
                    Call InsertValues("", "")   'gap between each site
                End If
               
        End Select
    Next ws
End Sub

Private Function SheetHasValues(ws As Worksheet) As Boolean
    Dim r As Long
    On Error Resume Next
    r = ws.Cells.Find("*", ws.Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
    If r > 1 Then SheetHasValues = True Else SheetHasValues = False
    On Error GoTo 0
End Function

Private Sub InsertValues(ByVal A, B)
    Sheets("Summary").Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 2) = Array(A, B)
End Sub

NOTE
Rule required to replace "count what ????"
VBA Code: 
Call InsertValues("Total DIDs:", "count what ????")

summaryOut.jpg
 
Upvote 0
Yongle said:
Place this code in a NEW module
Ensure that sheet "Summary" already exists
Hopefully code is all fairly self explanatory

VBA Code: 
Sub Summarise()
    Dim ItemCount As Long, c As Long, ws As Worksheet
    Sheets("Summary").Cells.Clear
  
    For Each ws In ThisWorkbook.Worksheets
        Select Case ws.Name
            Case "Summary", "SheetX", "SheetY"
                'do nothing with excluded sheets
            Case Else
                If SheetHasValues(ws) Then
                    Call InsertValues("Site:", ws.Name)                                             'Insert SiteHeader
                    For c = 1 To ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
                        ItemCount = WorksheetFunction.CountA(ws.Columns(c)) - 1
                        If ItemCount > 0 Then Call InsertValues(ws.Cells(1, c), ItemCount)          'Insert count
                    Next c
                    Call InsertValues("Total DIDs:", "count what ????")
                    Call InsertValues("", "")   'gap between each site
                End If
              
        End Select
    Next ws
End Sub

Private Function SheetHasValues(ws As Worksheet) As Boolean
    Dim r As Long
    On Error Resume Next
    r = ws.Cells.Find("*", ws.Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
    If r > 1 Then SheetHasValues = True Else SheetHasValues = False
    On Error GoTo 0
End Function

Private Sub InsertValues(ByVal A, B)
    Sheets("Summary").Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 2) = Array(A, B)
End Sub

NOTE
Rule required to replace "count what ????"
VBA Code: 
Call InsertValues("Total DIDs:", "count what ????")

View attachment 14810
Click to expand...
Thank you. The "count what????" would be sum of B3 to B6 for site AB. Similarly for site BC would be the sum of B9 to B11. So, count what would sum of values for each site. How can i embed this? Also is it possible to include space between the sites? Like after Total DIDs, leave the row blank and then start with site: BC. Also, would like to add another line into code that would count all the "Total DIDs" value across all sites (AB, BC, CD, EF...etc, etc..) in the summary page and appear in the bottom as "Final Total DID's Count"
 
Upvote 0
it will not be those ranges every time
what is the "generic" rule to be applied?
which headers are included?
or, if a simpler condition, which headers are always excluded?

When you reply, I will post updated code to include that plus grand total
 
Upvote 0
Yongle said:
it will not be those ranges every time
what is the "generic" rule to be applied?
which headers are included?
or, if a simpler condition, which headers are always excluded?

When you reply, I will post updated code to include that plus grand total
Click to expand...
Right. It won't be those ranges everytime. The headers are inconsistent in each tab. But as a generic rule, on each tab/sheet any header that doesn't have any values in it will be excluded and the total will be for the headers with values in it. For example on my initially attached screen-shot, sheet AB had headers DID Ranges, DID ranges in Use and Critical Numbers with values in it. so count all those values for 3 headers and present the sum in Total DIDs.
 
Upvote 0
summaryOut.jpg


The reason I asked about subtotals is the inconsistency in post#1
- see totals AB and BC in Summary.PNG

Amendments made:
Sub total for each site included
Grand total placed at bottom of summary
Optional variable added to InsertValues to allow blank rows between values

Delete previous code and replace with code below:
VBA Code: 
Sub Summarise()
    Dim ItemCount As Long, c As Long, ws As Worksheet, SubTotal As Long, GrandTotal As Long
    Application.ScreenUpdating = False
    Sheets("Summary").Cells.Clear
    For Each ws In ThisWorkbook.Worksheets
        Select Case ws.Name
            Case "Summary", "SheetX", "SheetY"
                'do nothing with excluded sheets
            Case Else
                If SheetHasValues(ws) Then
                    SubTotal = 0
                    Call InsertValues("Site:", ws.Name, 1)                                            'Insert SiteHeader
                    For c = 1 To ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
                        ItemCount = WorksheetFunction.CountA(ws.Columns(c)) - 1
                        If ItemCount > 0 Then
                            Call InsertValues(ws.Cells(1, c), ItemCount)          'Insert count
                            SubTotal = SubTotal + ItemCount
                        End If
                    Next c
                    Call InsertValues("Total DIDs:", SubTotal)
                    GrandTotal = GrandTotal + SubTotal
                End If
        End Select
    Next ws
    Call InsertValues("GrandTotal DIDs:", GrandTotal, 1)
    Sheets("Summary").Rows("1:2").EntireRow.Delete
End Sub

Private Sub InsertValues(ByVal A, B, Optional BlankRows As Integer)
    Sheets("Summary").Cells(Rows.Count, 1).End(xlUp).Offset(1 + BlankRows).Resize(, 2) = Array(A, B)
End Sub

Private Function SheetHasValues(ws As Worksheet) As Boolean
    Dim r As Long
    On Error Resume Next
    r = ws.Cells.Find("*", ws.Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
    If r > 1 Then SheetHasValues = True Else SheetHasValues = False
    On Error GoTo 0
End Function
 
Upvote 0
Yongle said:
View attachment 15019

The reason I asked about subtotals is the inconsistency in post#1
- see totals AB and BC in Summary.PNG

Amendments made:
Sub total for each site included
Grand total placed at bottom of summary
Optional variable added to InsertValues to allow blank rows between values

Delete previous code and replace with code below:
VBA Code: 
Sub Summarise()
    Dim ItemCount As Long, c As Long, ws As Worksheet, SubTotal As Long, GrandTotal As Long
    Application.ScreenUpdating = False
    Sheets("Summary").Cells.Clear
    For Each ws In ThisWorkbook.Worksheets
        Select Case ws.Name
            Case "Summary", "SheetX", "SheetY"
                'do nothing with excluded sheets
            Case Else
                If SheetHasValues(ws) Then
                    SubTotal = 0
                    Call InsertValues("Site:", ws.Name, 1)                                            'Insert SiteHeader
                    For c = 1 To ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
                        ItemCount = WorksheetFunction.CountA(ws.Columns(c)) - 1
                        If ItemCount > 0 Then
                            Call InsertValues(ws.Cells(1, c), ItemCount)          'Insert count
                            SubTotal = SubTotal + ItemCount
                        End If
                    Next c
                    Call InsertValues("Total DIDs:", SubTotal)
                    GrandTotal = GrandTotal + SubTotal
                End If
        End Select
    Next ws
    Call InsertValues("GrandTotal DIDs:", GrandTotal, 1)
    Sheets("Summary").Rows("1:2").EntireRow.Delete
End Sub

Private Sub InsertValues(ByVal A, B, Optional BlankRows As Integer)
    Sheets("Summary").Cells(Rows.Count, 1).End(xlUp).Offset(1 + BlankRows).Resize(, 2) = Array(A, B)
End Sub

Private Function SheetHasValues(ws As Worksheet) As Boolean
    Dim r As Long
    On Error Resume Next
    r = ws.Cells.Find("*", ws.Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
    If r > 1 Then SheetHasValues = True Else SheetHasValues = False
    On Error GoTo 0
End Function
Click to expand...
My Bad on inconsistency in post#1. I tried this and got the desired results. Just curious what if i need to exclude certain columns on the sheet from being considered. Like i want to exclude CC Numbers and Server Fax Numbers from being counted from every sheet although they contain values.
 
Upvote 0
Harry_1234 said:
My Bad on inconsistency in post#1. I tried this and got the desired results. Just curious what if i need to exclude certain columns on the sheet from being considered. Like i want to exclude CC Numbers and Server Fax Numbers from being counted from every sheet although they contain values.
Click to expand...
Would also like to know the other way around i.e. if i am only interested in CC Numbers and Server Fax Numbers from every sheet.
 
Upvote 0
Time to decide what would be best for the future so that solution ...
- provides a single report of everything required
- and (if necessary) additional options for further flexibility

To avoid user confusion ... consider making numbers not in subtotals very obvious!
Items could be marked with an asterisk within current layout (but that is a bit messy) or consider alternative layouts

eg - separate column for excluded items
Summary 1.jpg


eg - different column for each site with excluded items listed in lower section
(could insert a total column for all sites in Column B)
Summary 2.jpg


Post a picture if you prefer a different layout

Q Is the same list of headers to be excluded EVERY time, or would you want flexibility to exclude headers of your choosing at time of running report?
 
Upvote 0
Yongle said:
Time to decide what would be best for the future so that solution ...
- provides a single report of everything required
- and (if necessary) additional options for further flexibility

To avoid user confusion ... consider making numbers not in subtotals very obvious!
Items could be marked with an asterisk within current layout (but that is a bit messy) or consider alternative layouts

eg - separate column for excluded items
View attachment 15177

eg - different column for each site with excluded items listed in lower section
(could insert a total column for all sites in Column B)
View attachment 15176

Post a picture if you prefer a different layout

Q Is the same list of headers to be excluded EVERY time, or would you want flexibility to exclude headers of your choosing at time of running report?
Click to expand...
I was going through the same confusion when I posted this. I am trying to come up with best possible solution. I think the second one would be ideal i.e. different column for each site with excluded items listed in lower section and insert a total column for all sites for both included and Excluded and Grand Total. Also, would like the flexibility to exclude headers of my choice at the time of running report. To make it easier i know what needs to be included, the excluded is what could be varying and very extensive across each site. So i am envisioning something like today i run the report i could choose CC Numbers and Server Fax Numbers to be included across each site and the rest to be excluded. The next day if i decide to include Turpik Numbers then it would be CC Numbers, Server Fax Numbers and Turpik numbers across each site and rest to be excluded below. Posting a screen-shot of the layout.
 

Attachments

  • Summary-1.PNG
    Summary-1.PNG
    18.1 KB · Views: 10
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question
Move multiple rows from one table to another using conditional trigger
Replies
2
Views
453
ItsNursinTime
I
H
  • Solved
Comparing Data from multiple spread-sheets and fish out wanted data into a separate spread-sheet organized per tab
2 3
Replies
24
Views
2K
Harry_1234
H
C
  • Question
VBA Macros to Copy Specific Cells Values from Multiple Sheets to Specific Cells in Summary Sheets
Replies
2
Views
285
chittunoo
C
Q
  • Solved
VBA: Populate Data from Multiple Worksheets
2
Replies
10
Views
1K
Alex Blakenburg
A
B
  • Question
Using Macros to extract data from multiple Excel Wb's located in an independent directory and deposit that data into a 'Master' file
Replies
1
Views
752
Jon Peltier
J

Forum statistics

Threads
1,214,652
Messages
6,120,746
Members
448,989
Latest member
mariah3

Share this page

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
Back
Top