Hide Subtotals in Pivot Table for Certain Columns
 

I have a pivot table with the columns:


Vendor

Item

Description

Number of Stores with Item out of Stock

Quantity Required

Extended Cost

Model Stock

Order At



For each vendor I want to show the total order cost, however I do not
want subtotals on any of the other columns.


That is I need to know that I have to place an order for $1000 to a
vendor but it doesn't make sense to say 15 hammers plus 30 drills
equals 45.



I found the following macro which was very helpful however I want to
know if there is a way to be selective about which data fields should
be subtotaled and which should not

Sub NoSubtotals()
'turns off subtotals in pivot table
'.PivotFields could be changed to
'.RowFields or .ColumnFields
Dim pt As PivotTable
Dim pf As PivotField
On Error Resume Next
For Each pt In ActiveSheet.PivotTables
For Each pf In pt.PivotFields
'First, set index 1 (Automatic) to True,
'so all other values are set to False
pf.Subtotals(1) = True
pf.Subtotals(1) = False
Next pf
Next pt
End Sub


Sincerely

Jim P

Hide Subtotals in Pivot Table for Certain Columns
 

I worked out one solution but perhaps someone can suggest a better way?

I used Data Filter Advanced and set up criteria to display only rows
where column B, or column C or Column D ... were *Total

Then I highlighted all values in columns other than the cost column,
selected visible cells only and changed the font to white on white.

This wasn't as automated as I had hoped it would be, however it
achieved the results that I wanted.

Jim P

Jim P wrote:
I have a pivot table with the columns:


Vendor

Item

Description

Number of Stores with Item out of Stock

Quantity Required

Extended Cost

Model Stock

Order At



For each vendor I want to show the total order cost, however I do not
want subtotals on any of the other columns.


That is I need to know that I have to place an order for $1000 to a
vendor but it doesn't make sense to say 15 hammers plus 30 drills
equals 45.



I found the following macro which was very helpful however I want to
know if there is a way to be selective about which data fields should
be subtotaled and which should not

Sub NoSubtotals()
'turns off subtotals in pivot table
'.PivotFields could be changed to
'.RowFields or .ColumnFields
Dim pt As PivotTable
Dim pf As PivotField
On Error Resume Next
For Each pt In ActiveSheet.PivotTables
For Each pf In pt.PivotFields
'First, set index 1 (Automatic) to True,
'so all other values are set to False
pf.Subtotals(1) = True
pf.Subtotals(1) = False
Next pf
Next pt
End Sub


Sincerely

Jim P