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
Bookmarks