PDA

View Full Version : VBA PivotTables "Unable to get the PivotItems of the PivotField Class"



prks21
02-05-2015, 12:18 AM
Hey Guys,

I've been trying to solve this for most of the day and is really annoying. Please help!

I've been writing a macro to select an additional date in a date filter of a pivot table. I recorded the macro, as such:


Sub Macro4()
'
' Macro4 Macro
'
'
ActiveSheet.PivotTables("PivotTable2").PivotFields("Order_Date").CurrentPage = _
"(All)"
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Order_Date")
.PivotItems("5/02/2015").Visible = True
End With
End Sub

However, even when I try running that code I get an error. how is that even possible? Recorded macro not performing what it did....

Any help would be much appreciated. Thanks!

Bob Phillips
02-05-2015, 02:05 AM
It is VBA's problem with dates, it is US centric, so records US centric but yours dates in Excel are probably not (which is why it records okay, but then doesn't run).

What you need to do is force a date formatted as you have in the pivot field. In the example I show below, my date is 2nd May, and my format is d-mmm, you will need to change as per your values


With ActiveSheet.PivotTables("PivotTable2")

With .PivotFields("Order_Date")

.PivotItems(Format(DateSerial(2015, 5, 2), "d-mmm")).Visible = True
End With
End With


I did try to pick up the field's Numberformat property, there is one, but it seems to be indeterminate.

prks21
02-05-2015, 04:19 PM
It is VBA's problem with dates, it is US centric, so records US centric but yours dates in Excel are probably not (which is why it records okay, but then doesn't run).

What you need to do is force a date formatted as you have in the pivot field. In the example I show below, my date is 2nd May, and my format is d-mmm, you will need to change as per your values


With ActiveSheet.PivotTables("PivotTable2")

With .PivotFields("Order_Date")

.PivotItems(Format(DateSerial(2015, 5, 2), "d-mmm")).Visible = True
End With
End With


I did try to pick up the field's Numberformat property, there is one, but it seems to be indeterminate.

I ran your code (modified date format to "dd/mm/yyyy" and it gives the same error. Not sure why it is playing up.