shieldsco
asked on
Subtotals Multiple Excel Sheets
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
Can you upload a sample file?
See attached
Sum-all-sheet.xlsx
Sum-all-sheet.xlsx
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
thanks
ASKER
Harry - find attached sample. I only want to subtotal sheets with Emp Name
Test.xlsm
Test.xlsm
ASKER
Itjockey - it not sum all sheets - it's put subtotals on all sheets
Emp? or EPA?
ASKER
Itjockey - I want to subtotal each sheet that has a Employee Name - see sample
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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
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
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.
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.
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.
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.
ASKER
Harry - I'm also using data connections with Access so I sort before the data reaches Excel
ASKER
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#
Harry - I'm sorting on Project Name not Project#
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.