VBA to auto filter pivot table based on cell value

I have code that will filter a pivot table to only the value that I choose:

 

Sub TestPivot()
'
' TestPivot Macro
'

'
Dim pf As PivotField
Dim pi As PivotItem

Set pf = ActiveSheet.PivotTables("PivotTable4").PivotFields("GMI Qtr.Fiscal Year (Invoice)")
For Each pi In pf.PivotItems
    If pi.Name = "2.2013" Then
   
        pi.Visible = True
    Else
        pi.Visible = False
    End If
Next
End Sub

 

The above works, but what I want is the "2.2013" to actually reference a cell on another worksheet (worksheet name: Directions, cell B10). I have tried this every way I can think of. How do I make this work?

 

Thank you in advance!

 

Question Info


Last updated July 17, 2019 Views 20,432 Applies to:

Hi,

[Edit.... Feb 22, 2015]

Sub TestPivot2()
'TestPivot Macro

Dim dt As String
dt = Sheets("Directions").Range("B10").Value

With ActiveSheet.PivotTables("PivotTable4").PivotFields("GMI Qtr.Fiscal Year (Invoice)")

.ClearAllFilters
.PivotFilters.Add Type:=xlCaptionEquals, Value1:=dt
End With

End Sub

-----------------------------
Office 365 on Windows 10

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Thank you. I am a real novice at this, so I'm sorry if I'm missing something obvious. Just changed my code to include the above, and now I have this:

 

Dim pf As PivotField
Dim pi As PivotItem
Set pf = ActiveSheet.PivotTables("PivotTable4").PivotFields("GMI Qtr.Fiscal Year (Invoice)")

 

Dim dt As String
dt = Sheets("Directions").Range("B10").Value
For Each pi In pf.PivotItems
pi.Visible = True

If pi.Value <> dt Then pi.Visible = False
Next

 

It bombs out at "pi.Visible = True".

 

Any idea?

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Actually, I got it to work, sort of....

 

Here's the code:

 

Dim dt As String
dt = Sheets("Directions").Range("Z10").Value
Dim pf As PivotField
Dim pi As PivotItem

Set pf = ActiveSheet.PivotTables("PivotTable4").PivotFields("GMI Qtr.Fiscal Year (Invoice)")
For Each pi In pf.PivotItems
   If pi.Name = dt Then
        pi.Visible = True
    Else
        pi.Visible = False
    End If
Next

That works except it still leaves one other option on the filter!!

 

Options on the filter are:
1.2014

2.2014

3.2014

4.2014

(blank)

#N/A

 

If I run the above macro when I have "2.2014" in cell Z10, then it hides everything except 2.2014 and 1.2014. Why is it including 1.2014???!

 

Same thing happens with this code:

 

'Dim pf As PivotField
Dim pi As PivotItem

Set pf = ActiveSheet.PivotTables("PivotTable4").PivotFields("GMI Qtr.Fiscal Year (Invoice)")
For Each pi In pf.PivotItems
   If pi.Name = Sheets("Directions").Range("Z10").Value Then
        pi.Visible = True
    Else
        pi.Visible = False
    End If
Next

 

Please help!

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Reply In reply to deleted message

For your problem about extra values getting filtered:

If I run the above macro when I have "2.2014" in cell Z10, then it hides everything except 2.2014 and 1.2014. Why is it including 1.2014???!

Add this line to your code:

ActiveSheet.PivotTables("PivotTable4").PivotFields("GMI Qtr.Fiscal Year (Invoice)").ClearAllFilters

So now your code becomes this:

Dim dt As String
dt = Sheets("Directions").Range("Z10").Value
Dim pf As PivotField
Dim pi As PivotItem

ActiveSheet.PivotTables("PivotTable4").PivotFields("GMI Qtr.Fiscal Year (Invoice)").ClearAllFilters

Set pf = ActiveSheet.PivotTables("PivotTable4").PivotFields("GMI Qtr.Fiscal Year (Invoice)")
For Each pi In pf.PivotItems
   If pi.Name = dt Then
        pi.Visible = True
    Else
        pi.Visible = False
    End If
Next

And this perfectly works. It won't show any extra values getting filtered. I was facing the same problem but now it works. What it used to do is store that one value in array and then every instance you run the macro code, it'll not clear that previous value stored. The additional line suggested by me would help you clear the array memory

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Hi,

try this,

Sub VisibleOneItem_01()

'Feb 21, 2015

On Error Resume Next
Dim dt As String
dt = Sheets("Directions").Range("Z10").Value

With ActiveSheet.PivotTables("PivotTable4").PivotFields("GMI Qtr.Fiscal Year (Invoice)")

.ClearAllFilters
.PivotFilters.Add Type:=xlCaptionEquals, Value1:=dt
End With
End Sub

-----------------------------
Office 365 on Windows 10

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

What if I have to do the same auto filter thing coding according to cell value, but for simple columns, and not pivot table? How will the code change? Thanks. 

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Hi,

this issue has been solved/answered.
So, you may write a new query.

-----------------------------
Office 365 on Windows 10

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.