VBA code for using a cell value to change pivot filter

Good day: I am trying to change a pivot filter field by pointing it to a cell value in a sheet.
using the below code :

Sub pvtdate()


Dim dt As String
dt = Sheets("pivot").Range("D1").Value
With ActiveSheet.PivotTables("PivotTable10").PivotFields("[Time].[Month Name].[Month Name]")

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

End Sub

Where value in D1 is September. so dt = September
getting "run-Time error '1004':"

the pivot filter field is stored in a cube which is the source of the data
when i record changing the fields manually i get below code:

    ActiveSheet.PivotTables("PivotTable10").PivotFields( _
        "[Time].[Month Name].[Month Name]").ClearAllFilters
    ActiveSheet.PivotTables("PivotTable10").PivotFields( _
        "[Time].[Month Name].[Month Name]").CurrentPageName = _
        "[Time].[Month Name].&[April]"

so with recording it seems it uses [Time].[Month Name].&[April]".
tried changing dt to say &[September] and still nothing.


also tried the below code and it runs but selects everything under the month name:

Sub pvtdate()

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

ActiveSheet.PivotTables("PivotTable1o").PivotFields("[Time].[Month Name].[Month Name]").ClearAllFilters

Set pf = ActiveSheet.PivotTables("PivotTable10").PivotFields("[Time].[Month Name].[Month Name]")
For Each pi In pf.PivotItems
   If pi.Name = dt Then
        pi.Visible = True
    Else
        pi.Visible = False
    End If
Next

End Sub

EDIT NOTE: this is using OLAP fields so I think I am going wrong with PivotItem.Name property

any ideas here on where I am going wrong?

Thanks

If you just want a single value. You can use

Range("Pivot Filter Cell") = Range("Cell to Read")

This works OK for text values. However, there is a caveat. If you do this and the value you are trying to set does not exist in the source data, then you will break the pivot table. Suppose you had "Company A" already in the filter and you use to the code to "select" "Company B" and Company B does not exist in the data. Then the pivot table will assign Company B to replace Company A in the filter list. Company A will not appear in the filter list and if you select Company B, you will get the former Company A data.

So, you need to do a check. While you have the value in the cell, in another cell do a COUNTIF() against the source data to see if that value exists. If the count is greater than zero, you can set the filter. If it is zero, skip setting the filter.

Pivot table filters work on strings, so if you want True or False, you need to pass "True" or "False." Likewise, date values need to be passed as their string equivalents in the format shown in the pivot filter.

The issue with trying to change the filter the way you are changing it (which is the right idea) is that a pivot table must always have at least one item selected. If you get to a condition where all the visible are false, the code will fail. In other words, you have 5 items and they are True, True, True, False, False and you want to change them to False, False, False, True, True. The code will work until it hits the third item. I has no problem turning the first two to false but when you try to turn the thrid item to false, all the items are false and Excel doesn't like that. It does not know that you intend to turn a later item to True.

So you will have code prior to the loop you have and turn everything ON, then loop and turn off those items you don't want.

I can explain better if you can provide a sample workbook.



Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.