Creating % of Grand Total for Selection.Subtotal formula

[kyrgan]

Hello all,

I am using the following to create subtotals from a selection

    Range("B3:N1000").Select
    Selection.Subtotal GroupBy:=2, Function:=xlSum, _
    TotalList:=Array(5, 6, 7)
    ActiveSheet.Outline.ShowLevels RowLevels:=2

I need to add a column with the sales percentage: the Net Sales (Column 7) divided by the Grand Total (Last Row, Column 7) as a formula in Column 8 [% of sales]. Or even the results of this calculation there.

With the preceding code, what is nice this is I get a final new row that shows the Grand Total.

What I would like to do is when there is a Net Sales value in column 7 , to insert the formula =(Net Sale / Grand Total ). There is rarely the same number of rows so the Grand Total is rarely on the same row.

 

[Fazza]

hi

what about adding a formula in column 8 before the sub-totals are added =f2/(sum(f:f)*3

when adding sub-totals, add them to column 8 also

the solution relies on column F ultimately having the base data, and sub-totals and grand totals. So three times the base data

hth. regards