I want to show the percent of column a row has for the subtotal in my pivot
table. See example below. I have figured out how to do this if I use the
option to have subtotals appear a the bottom and use a function just outside
the pivot table to the right. However, I prefer my subtotals at the top and
there is no text in the top row like in the bottom that says "XXX subtotal".
I started to write a macro that would do this but I can't figure out how to
determine if a row is a subtotal row. Any ideas on how to determine if a
particular row is a subtotal row?

I was hoping that activecell.pivotcell.pivotcelltype would tell me but it
gives me the same value for both a data row and a subtotal row. I even
thought about looking for the format style when I applied a formating style
to the table. IE. if it's bold it's a subtotal field. But that did not
work either.

Any ideas?
Blue 80
item1 50 62.5% 50/80
item2 20 25% 20/80
item3 10 12.5% 10/80
Red
item4
item5
Gren
item6
item7

I finally figured out how to accomplish my goal and I thought I would post
the answer here as well.


What this macro does is one column to the right of the pivot table it will
insert the percent of that row to the subtotal and not grand total, when you
have subtotals placed at the top.

The trick to all of this is determining the subtotal row in the pivot table.
I did this by looking at labelrange.address, getting the row number for the
label row, then comparing it to the row I am currently inserting the
percentage for.


ActiveCell.PivotCell.RowItems.Item(1).LabelRange.A ddress,
Sub PivotTableCreateSubTotals()
Dim pitem As Excel.PivotItem
Dim pvt As Excel.PivotTable
Dim r As Range
Dim col As Range
Dim i As Integer
Dim s() As String

Set pvt = ActiveSheet.PivotTables(1)

Set r = pvt.RowRange
Set col = pvt.ColumnRange



For i = 2 To r.Rows.Count - 1
r.Cells(i, col.Columns.Count + 1).Select
s = Split(ActiveCell.PivotCell.RowItems.Item(1).LabelR ange.Address,
"$")
If s(2) = ActiveCell.Row Then
ActiveCell.Offset(0, 1).Value = ""
Else
ActiveCell.Offset(0, 1).Value = ActiveCell.Value / Cells(s(2),
ActiveCell.Column)
End If
Next


End Sub

"Brady" wrote:

I want to show the percent of column a row has for the subtotal in my pivot
table. See example below. I have figured out how to do this if I use the
option to have subtotals appear a the bottom and use a function just outside
the pivot table to the right. However, I prefer my subtotals at the top and
there is no text in the top row like in the bottom that says "XXX subtotal".
I started to write a macro that would do this but I can't figure out how to
determine if a row is a subtotal row. Any ideas on how to determine if a
particular row is a subtotal row?

I was hoping that activecell.pivotcell.pivotcelltype would tell me but it
gives me the same value for both a data row and a subtotal row. I even
thought about looking for the format style when I applied a formating style
to the table. IE. if it's bold it's a subtotal field. But that did not
work either.

Any ideas?
Blue 80
item1 50 62.5% 50/80
item2 20 25% 20/80
item3 10 12.5% 10/80
Red
item4
item5
Gren
item6
item7