Why doesn't PivotField.CurrentPage work with dates?

Question

When I record a macro when changing the filter of a pivot table, the code is:

ActiveSheet.PivotTables("BTW-verkopen").PivotFields( \_ 

    "[Verkopen].[Fact.datum (Jaar)].[Fact.datum (Jaar)]").ClearAllFilters 

ActiveSheet.PivotTables("BTW-verkopen").PivotFields( \_ 

    "[Verkopen].[Fact.datum (Jaar)].[Fact.datum (Jaar)]").CurrentPage = \_ 

    "[Verkopen].[Fact.datum (Jaar)].&[2023]" 

The filter [Verkopen}.[Fact.datum (Jaar)] is the Year group that was created when creating the pivot table.

Please note that the macro uses the CurrentPage property!

The following code is intended to be used for creating a filter value based on a cell value:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim draaiTabelBtwVerkopen As PivotTable 

Dim filterVeldBtwVerkopen As PivotField 

Dim filterWaarde As String

If Intersect(Target, Range("C4")) Is Nothing Then Exit Sub 

Application.ScreenUpdating = False 

Set draaiTabelBtwVerkopen = Worksheets("Omzetbelasting").PivotTables("BTW-verkopen") 

Set filterVeldBtwVerkopen = draaiTabelBtwVerkopen.PivotFields("[Verkopen].[Fact.datum (Jaar)].[Fact.datum (Jaar)]") 

filterWaarde = Target.Text 

filterVeldBtwVerkopen.ClearAllFilters 

filterVeldBtwVerkopen.CurrentPage = filterWaarde 

Application.ScreenUpdating = True 

End Sub

When running the code it generates an error message saying that the CurrentPage property can't be assigned to the PivotField class. This issue seems to be existing for years now and it looks as if the issue only occurs when the filter value you want to assign is (a) date (related). I can't find a solution for the issue, even after spending a lot of time searching and trying so some help would be much appreciated.

Jan

Answer 1

Hello Rory,

Your suggestion was tried earlier by me but from your reply I learned that I made a mistake with the concatenation symbol. Doing it the right way and using your code however brings me back to the original CurrentPage issue.

I will keep trying to solve the problem and will report a solution.

Regards,

Jan

Answer 2

You can't use PivotFilters.Add (or Add2) with a page filter.

Answer 3

It's possible that the error message "Error 5 Error during execution.: Illegal procedure call or invalid argument" is related to the arguments that you are passing to the Add method. Make sure that the Type and Value1 arguments are correctly passed and that the date format is correct.

It's also worth noting that the Add2 method is an alternative to the Add method, which is available in later versions of Excel. It might be that the Add method is not supported in your version of Excel and you should use Add2 instead.

Answer 4

Hello Anna,

Thank you for your reply.

I've been on the track you are suggesting but have given it another try and changed the code as you suggested. Once again an error is popping up: Error 5 Error during execution.: Illegal procedure call or invalid argument.

During coding the editor is suggesting only the use of the Add2, not the Add. However both give the same error message.

I will try the calculated field option later today.

Kind regards,

Jan

Answer 5

Hi Jan

I'm Anna and I'd be happy to help you with your question. In this Forum, we are Microsoft consumers just like yourself.

The CurrentPage property is used to filter a pivot field based on a specific item or items within that field. However, it is not designed to work with date fields. Instead, you can use the PivotField.PivotFilters.Add method to filter a pivot field based on a date range or specific date values. This method allows you to specify the type of filter (e.g. "greater than or equal to", "between", etc.) and the filter value(s) to use.

For example, to filter a pivot field called "Fact.datum (Jaar)" to show only data for the year 2022, you could use the following code:

filterVeldBtwVerkopen.PivotFilters.Add Type:=xlFilterValues, Value1:="2022" You could also use PivotField.ClearAllFilters before adding the new filter to ensure that any existing filters are removed.

Alternatively, you can use a calculated field to show only the specific year using the filter of that calculated field.

It's worth noting that the property CurrentPage is not designed for date fields and it will give you an error when trying to apply it on a date field, that's why you are getting the error message you are seeing.

I hope this helps ;-), let me know if this is contrary to what you need, I would still be helpful to answer more of your questions.

Best Regards,

Anna

Give back to the community. Help the next person with this problem by indicating whether this answer solved your problem. Click Yes or No at the bottom.