Hide Subtotals in Pivot Table for Certain Columns


J

Jim P

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
 
Ad

Advertisements

J

Jim P

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top