vmccune
asked on
VBA for subtotal and highlight the rows
I have a small spreadsheet That I want to do some subtotals and shading on. I have enclosed a sample with a tab of Before and a tab of After to illustrate what I want to do using VBA. Thanks!
VM
Example.xlsx
VM
Example.xlsx
ASKER
The actual sheets I will be using this with are much larger and have several tabs. I really want to automate it with VBA.
The Subtotal function can handle large data sets on each tab but would have to be automated to cover multiple tabs.
I can write a quck VBA script to apply the subtotal and format to all worksheets.
Be back shortly.
I can write a quck VBA script to apply the subtotal and format to all worksheets.
Be back shortly.
ASKER
can you also add the variance and pct calculations?
Thanks.
Thanks.
VBA Code to do as requested to ALL sheets. Do you know where and how to apply it?
If it shouldn't be applied to all sheets, you will have to specify which ones to omit but should be able to accommodate that.
If the formatting isn't right adjust the values in the "Apply Format section". To get the correct settings, start the VBA Recorder and apply the formatting required. Then stop the recorder and copy the result into this script.
Thanks
Rob H
If it shouldn't be applied to all sheets, you will have to specify which ones to omit but should be able to accommodate that.
Sub Apply_Subtotal()
For Each Worksheet In ActiveWorkbook.Worksheets
'Select Sheet
ShtName = Worksheet.Name
Sheets(ShtName).Select
Range("A1").Select
'Apply Subtotals
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2, 3, 4, 5), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
'Apply Filter
Worksheets(ShtName).Range("A1").AutoFilter _
field:=1, _
Criteria1:="=*total*", _
VisibleDropDown:=True
'Apply Format
Selection.CurrentRegion.Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent5
.TintAndShade = 0.399975585192419
.PatternTintAndShade = 0
End With
'Remove Format from Header Row
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveSheet.ShowAllData
Selection.AutoFilter
Range("A1").Select
Next Worksheet
End Sub
If the formatting isn't right adjust the values in the "Apply Format section". To get the correct settings, start the VBA Recorder and apply the formatting required. Then stop the recorder and copy the result into this script.
Thanks
Rob H
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The Percent change in the subtotal is summing the percent. It should be the same calculation. The variance is fine as a calculation.
There is the Subtotal function that will insert the subtotal rows and do the grouping for you. This is on the Data tab, Outline Group.
If you then collapse to total rows and apply the formatting it formats all rows rather than just visible rows.
However, if leave expanded and apply a filter and filter the total column for "Contains Total" when applying the formatting it will only apply to visble cells.
Thanks
Rob H