Change the pivot Table filter using VBA

7
votes

I have a pivot table which contains the "CoB Date" field as shown.
I am trying to create a macro which automatically changes the date as per the user input.
I've written the following macro code. But it shows the error:

Unable to get PivotFields property of the PivotTable class

Can any one help me with this?
Note: Assume that Date Format is not an issue

Code:

Sub My_macro()
    Dim num as String
    num = InputBox(Prompt:="Date", Title:="ENTER DATE")
    Sheets("Sheet1").PivotTables("PivotTable1") _
        .PivotFields("CoB Date").CurrentPage = num
End Sub

enter image description here

2
vbaexcelexcel-addins
Perhaps you could share the code you have and tell us what errors and where they occur... – Rory
@Rory: Yeah, I've added the vba code as well – pikachuchameleon
To get what you want, 1st you have to make sure the user enters the correct date format. Once you solve that, you can simple use the Pivot Table Objects CurrentPage method to update the currently selected date. Btw, we are talking about normal pivots here right? not Power Pivots? Your code didn't work because you are trying to access a Range which is part of the Pivot. – L42
@L42: Yeah, normal pivots only. I'll try to figure out in the way you've mentioned. Thanks – pikachuchameleon
Cool. If ever you got stuck in the process, update your question. – L42

2 Answers

3
votes

As commented the exact same code works on my end.

Sub My_macro()
    Dim num As String
    num = InputBox(Prompt:="Date", Title:="ENTER DATE")
    Sheets("Sheet1").PivotTables("PivotTable1") _
        .PivotFields("CoB Date").CurrentPage = num
End Sub

Suppose you have a data like this:

enter image description here

When you run the macro, it will prompt for a date:

enter image description here

And then after pressing ok, the result would be:

enter image description here

Take note that we assumed that entering of date is not an issue.
So we used a simple data which will eliminate that and so your code works.
The probable issue you're dealing with is if the dates have Time Stamp.
And based on your screen shot, that is the case.

1
votes

I had the same problem with "Unable to get PivotFields property of the PivotTable class".

I figured out that while my pivot table was the only one on that sheet (or in the workbook for that matter) it was not "PivotTable1". It was "PivotTable4" most likely because I had created and deleted 3 others beforehand.

To find out the name of your pivot table, and change it if you want, just right click anywhere in your pivot table and then select "Pivot Table Options". You will see the "PivotTable Name" right at the top and can rename it from there.

Additionally, I was having issues with this same error when trying to change one of the filters. When I referenced the field using:

Sheets("mySheetName").PivotTables("PivotTable4").PivotFields("myFieldName")

it would throw the same error as above. It turned out I had 3 spaces at the end of my field name. The way I found this out may was to loop through all my filter fields displaying a MsgBox for each until I found it. The following code is how I did that and hopefully may help someone with a similar issue:

Dim pt As PivotTable
Dim pf As PivotField

Set pt = Sheets("mySheetName").PivotTables("PivotTable4")
For Each pf In pt.PivotFields
    MsgBox ("FieldName: [" & pf.Name & "] with a length of: " & Len(pf.Name) & " and a trimmed length of: " & Len(Trim(pf.Name)))
Next pf

Hope this helps someone!