Hi,
How can I change the filter content correctly by using "Currentpage" in an Excel Pivot table.
Pivot table name: L
PivotField name: Report Date
I simplize the code as below:
rd = InputBox("Plz input the report date. e.g. 4/20")
ActiveWorkbook.RefreshAll
Sheets(1).PivotTables("L").PivotFields("Report Date").CurrentPage = Format(rd, "m/d/yyyy")
It did work perfectly when the date actually exist in the source data, however, if the date does not exist in the source data, this code would still run successfully with no error occured, BUT the outcome of this filtering would remain the same, meanwhile overwrite the last filtering (you can never find the date you filtered last time in the filter list)
I am not sure if I should insert some certain code or use another function aside from "CurrentPage".
Thanks a lot!