Subtotals for A and B column in VBA

[trprasad78]

Hi all

I have data in sheet1 i need add sub totals for A and B Columns

subtotals should be added using vba code

Bit difficult to explain, please refer attached file , i have given expected out put.

please do the needful.

Thank you

Prasad

 

[Monty]

Hey Prasad.

Try this!

 

[trprasad78]

Hey Prasad.

Try this!

thank you, but its giving only subtotal for B, I Need subtotal for A and B COLUMN as shown in the output sheet.

 

[trprasad78]

also GM% Should be REVENUE / GM , Not total of %

please do the needful

 

[trprasad78]

i got the code, But i would like to bold the subtotal
any idea how to make bold the subtotals and grand total ? in below code

Sub Subtotal()
Range("a4").Select

 Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWindow.SmallScroll Down:=3
    Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(6, 7, 8, _
        9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19), Replace:=False, PageBreaks:=False, _
        SummaryBelowData:=True
    Range("B4").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Range("B4:T692").Select
    Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(6, 7, 8, _
        9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19), Replace:=False, PageBreaks:=False, _
        SummaryBelowData:=True
       
         Range("a4").Select
End Sub

 

[p45cal]

This is fairly standard pivot table stuff.
In the attached, next to your expected output table is a pivot table.
Currently it shows only Vendors 1 and 2 to match your expected output. It only takes an adjustment of the filter in cell K1 to include others (or all) the vendors.
You'll also note that the percentages are correct.

Regarding making totals and subtotals bold then maybe something like:

    ActiveSheet.Outline.ShowLevels RowLevels:=3
    Range("E1").CurrentRegion.SpecialCells(xlCellTypeVisible).Font.Bold = True
    ActiveSheet.Outline.ShowLevels RowLevels:=4

but since your code obviously works on a different range from your sample sheet I don't know exactly which columns they're in.
This is not necessary in a pivot table.

 

[Arpanakumar]

Wow that's so simple you made it.

 

[trprasad78]

This is fairly standard pivot table stuff.
In the attached, next to your expected output table is a pivot table.
Currently it shows only Vendors 1 and 2 to match your expected output. It only takes an adjustment of the filter in cell K1 to include others (or all) the vendors.
You'll also note that the percentages are correct.

Regarding making totals and subtotals bold then maybe something like:

    ActiveSheet.Outline.ShowLevels RowLevels:=3
    Range("E1").CurrentRegion.SpecialCells(xlCellTypeVisible).Font.Bold = True
    ActiveSheet.Outline.ShowLevels RowLevels:=4

but since your code obviously works on a different range from your sample sheet I don't know exactly which columns they're in.
This is not necessary in a pivot table.

Thank you so much its working great