Pivot table macro to INCLUDE rather than EXCLUDE items

mblander

New Member
Joined
Oct 19, 2009
Messages
24
My data set will vary in items. For example, the first data set will have JARS, BOTTLES, CUPS. The next time I run it, it might have JARS, BOTTLES, CUPS, JUGS. In both cases, I'd like the pivot table to show only JARS and BOTTLES. When I write the macro to create a pivot table, I am currently using:

With ActiveSheet.PivotTables("PivotTable6").PivotFields("Description")
.PivotItems("CUPS").Visible = False
.PivotItems("JUGS").Visible = False

The problem being, when the macro runs on data set 1 above, it will bomb out because there are no JUGS in the data set.

Is there a way to write it so that it will include JARS and BOTTLES rather than excluding CUPS and JUGS?
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
See if this works for you:
(Ref: Debra Dalgleish:- How to Show and Hide Excel Pivot Table Items)

VBA Code:
Sub ShowHidePivotItems()

    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
    
    Set pt = ActiveSheet.PivotTables("PivotTable6")
    Set pf = pt.PivotFields("Description")
    
    For Each pi In pf.PivotItems
        If InStr("JARS, BOTTLES", pi.Name) > 0 Then
            pi.Visible = True
        Else
            pi.Visible = False
        End If
        
    Next
    
End Sub
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,703
Members
448,293
Latest member
jin kazuya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top