I am trying to use date from a cell (text) to filter my pivot table and thought I understood well enough to complete it but can't seem to get it right. It is probably something simple but I can't find the solution to the problem and have tried different methods to make it work but nothing is working for me. Below is my latest attempt. What am I doing wrong with this?
Sub NewTry()
Dim Field As PivotField
Dim pt As PivotTable
Dim TopItem As String
Set pt = Worksheets("Trim Total").PivotTables("PivotTop10")
Set Field = pt.PivotFields("Material Description")
TopItem = Worksheets("FY17 YTD Pivot Tables").Range("A19").Value
' Used the following line to ensure that the TopItem value was caputured - it showed up as expected in the T91 cell as expected.
' Worksheets("Trim Total").Range("T91") = TopItem
With pt
' The clear filters line works as it clears any active filters in the pivot table.
Field.ClearAllFilters
' The next line creates:
' Run-time error '1004":
' Application-defined or object-defined error
Field.CurrentPage = TopItem
pt.RefreshTable
End With
Sub NewTry()
Dim Field As PivotField
Dim pt As PivotTable
Dim TopItem As String
Set pt = Worksheets("Trim Total").PivotTables("PivotTop10")
Set Field = pt.PivotFields("Material Description")
TopItem = Worksheets("FY17 YTD Pivot Tables").Range("A19").Value
' Used the following line to ensure that the TopItem value was caputured - it showed up as expected in the T91 cell as expected.
' Worksheets("Trim Total").Range("T91") = TopItem
With pt
' The clear filters line works as it clears any active filters in the pivot table.
Field.ClearAllFilters
' The next line creates:
' Run-time error '1004":
' Application-defined or object-defined error
Field.CurrentPage = TopItem
pt.RefreshTable
End With
