Solved

Subtotals Multiple Excel Sheets

Posted on 2014-04-04
15
1,193 Views
Last Modified: 2014-04-06
I would like to use VBA to Subtotal Multiple Excel Sheets. For Each change in the Project Name column I want to use the sum function and add the subtotal to the Balance Amount. Not all sheets in the Workbook will be subtotaled. Thanks
0
Comment
Question by:shieldsco
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 3
  • +1
15 Comments
 
LVL 9

Expert Comment

by:nick2253
ID: 39978697
Using this snippet will let you select data in other sheets.

ThisWorkbook.Sheets("name of sheet 2").Range("A1")

Open in new window

Depending on how the data is laid out in the sheet, you may need to change that from a single cell to a range, and then process on that range.
0
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39978701
Can you upload a sample file?
0
 
LVL 8

Expert Comment

by:itjockey
ID: 39978715
See attached
Sum-all-sheet.xlsx
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 8

Expert Comment

by:itjockey
ID: 39978725
it is simple formula based which SUM the all sheet Cell K2 which is between sheet "Start - Finish".so if any new sheet you insert between this two sheets sum total change accordingly.

thanks
0
 

Author Comment

by:shieldsco
ID: 39978734
Harry - find attached sample. I only want to subtotal sheets with Emp Name
Test.xlsm
0
 

Author Comment

by:shieldsco
ID: 39978741
Itjockey - it not sum all sheets - it's put subtotals on all sheets
0
 
LVL 8

Expert Comment

by:itjockey
ID: 39978749
Emp? or EPA?
0
 

Author Comment

by:shieldsco
ID: 39978769
Itjockey - I want to subtotal each sheet that has a Employee Name - see sample
0
 
LVL 12

Accepted Solution

by:
Harry Lee earned 500 total points
ID: 39978919
shieldsco,

I have came up with this VBA. Please test. Currently, instead of replacing the Balance column, I have added an extra column between the Invoice Amount and Balance Amount called Project Total. I can change it if you don't like it.

I had also put as much comments as I can come up with in the code to make it easy for you to understand what the code is doing.

Sub Subtotaling()
Dim WS As Worksheet, RWs As Long, CLMNs As Long, StartAddr As String, EndAddr As String, I As Long

'Work with sheets only if name is not equal to Summary or Invoice
For Each WS In Worksheets
    If WS.Name <> "Summary" And WS.Name <> "Invoice" Then
        
'Add new balance column if new balance column does not exist
        If WS.Range("G1") <> "Project Total" Then
            WS.Range("G:G").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
            WS.Range("G1") = "Project Total"
        Else
            WS.Range("G2:G" & Cells(Rows.Count, 1).End(xlUp).Row).Clear
        End If

    RWs = WS.Cells(Rows.Count, 1).End(xlUp).Row
'Correct Number stored as text
        Dim I2 As Object
        For Each I2 In WS.Range("J2:J" & RWs)
            I2 = I2.Value
        Next I2

'Sort all columns first by Project # then by Date
    CLMNs = WS.Cells(1, Columns.Count).End(xlToLeft).Column
    WS.Sort.SortFields.Clear
    WS.Sort.SortFields.Add Key:=Range("J:J"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    WS.Sort.SortFields.Add Key:=Range("B:B"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With WS.Sort
        .SetRange Range(Cells(1, 1), Cells(RWs, CLMNs))
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

'Apply Subtotal to New

    For I = 2 To RWs
        If WS.Cells(I, 10) <> WS.Cells(I - 1, 10) Then
            StartAddr = WS.Cells(I, 6).Address
        End If
        
        If WS.Cells(I, 10) <> WS.Cells(I + 1, 10) Then
            EndAddr = WS.Cells(I, 6).Address
            WS.Cells(I, 7).Formula = "=sum(" & StartAddr & ":" & EndAddr & ")"
        End If
    Next

'Auto fit column widths
        Range("A1").Select
        WS.Columns.AutoFit
    End If
Next
End Sub

Open in new window

0
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39978934
Forgot to mention to you. Your original macro has the hot-key set to Ctrl-A.

I would highly recommend you to change the hot-key to something else. Ctrl-A is default to Select All in Excel. If you use Ctrl-A as your macro hot-key, you loose the Select All default hot-key.

Also, once you are happy with my VBA, I would highly suggest you to combine my VBA with your Invoice macro by copying my code starting from the Dim all the way to the line right before End Sub, and paste it to the back of the Invoice macro. This way, you don't have 2 steps. By doing it all at once, you will avoid errors by changing where the columns are.
0
 

Author Closing Comment

by:shieldsco
ID: 39978936
Harry Thanks - see my code below

Sub SubTotals()
    Dim LastRow As Long
    Dim wsDst As Worksheet
     
    For Each wsDst In ThisWorkbook.Sheets
        If wsDst.Name <> "Invoice" And wsDst.Name <> "Summary" Then
            With wsDst
                LastRow = .Range("A" & Rows.Count).End(xlUp).Row
                .Range("A1:T" & LastRow).Subtotal GroupBy:=10, Function:=xlSum, TotalList:=Array(7), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
            End With
        End If
    Next
     
End Sub
0
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39978958
That works too. What you are doing is to use the internal subtotal function, which will create whole brunch of + and - buttons on the Row number area.

Also, it's inserting extra subtotal rows on the spreadsheet. In my opinion, those extra rows make it extremely hard to read the spreadsheet.

Opps! I think I misunderstood your question. You want to sub the Balance column instead of the Invoice Amount column.

Let me know if you want me to change the vba so that it sums the Balance Column as well.
0
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39978997
Regardless which way you want to go,

I think you need to make some modification to your VBA. Currently, there is error in there due to the sorting of the Project # or Project Name.

Look at the Karla Keppner tab, and Project# 112368. It's being broken down to multiple pieces and there are more than 1 subtotal for the Project.

Refer to my VBA for the Fixing Number stored as Text section, and the Sorting section. You can incorporate those 2 sections to your vba to fix such issue.
0
 

Author Comment

by:shieldsco
ID: 39981506
Harry - I'm also using data connections with Access so I sort before the data reaches Excel
0
 

Author Comment

by:shieldsco
ID: 39981510
Look at the Karla Keppner tab, and Project# 112368. It's being broken down to multiple pieces and there are more than 1 subtotal for the Project.

Harry - I'm sorting on Project Name not Project#
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question