Thread: Linking PivotItems to Specific Cells

Hey,

I'm currently designing a spreadsheet that uses pivot tables and I want to automate it by showing/hiding specific pivot items based on cell values that are updated. Here is what I have so far. It works but it's too long and I'm sure there's an easier way to code it:

Sub ShowHideDates()
Dim pi As PivotItem
Dim pt As PivotTable

Application.ScreenUpdating = False
On Error Resume Next

'Hide all pivot table items.
For Each pi In ActiveSheet.PivotTables("xxx").PivotFields("Date").PivotItems
pi.Visible = False
Next pi

DateH1 = Range("A3").Value
DateH2 = Range("A4").Value
DateH3 = Range("A5").Value
DateH4 = Range("A6").Value
etc.

With ActiveSheet.PivotTables("YTD PY Region Core").PivotFields("HC Date")
.PivotItems(DateH1).Visible = True
.PivotItems(DateH2).Visible = True
.PivotItems(DateH3).Visible = True
.PivotItems(DateH4).Visible = False
....etc. etc.
End With
End Sub

The problem is that I have more than four dates (I have about 50+) and I felt it's inefficient to set a string for every single date. What is a better way to do this? I'm sure it's really simple. Please help and thanks in advance!!

Sorry, but I don't think that helped.

Is there a way for me to code it based on criteria? For example:

My pivot table has pivot items with all dates from 1/01/2006 - 1/31/2007
I wish to show/hide certain dates, for example, show dates greater than 1/31/06 and less than 6/31/06. These criteria are placed in a specific cell range.

However, the criteria will change every month. Is there a way I can code VBA to show/hide specific pivot items based on these criteria?

Many thanks.

I've taken a look at that before, but it doesn't really help me to hide/show pivot items based on a specific set of criteria, at least without have to use an inputbox. If what I want to do is actually in there somewhere, can you point me to the piece of code I'm looking for? Thanks in advance.