I am attempting to manipulate what is selected (or not) in the report filter of a pivot table using variable names. I have tried my codee with hardcoded values and with variables, and it works with hardcoded values. So, I know the root cause of my issue is simply the use of the variable in the VBA command. Can anyone tell me the correct syntax to use here?
What I really want to do is within a more complex pivot table, only show a few pivot items. I want to loop through all items and only make visible certain ones.
For simplicity, I've generated a VERY simple pivot table and put the names of cars in the report filter.
Here is the hardcoded code that DOES work:
' Make all report fields visible
ActiveSheet.PivotTables("PivotTable1").PivotFields("Cars").CurrentPage = _
"(All)"
' Allow the user to select multiple selections
ActiveSheet.PivotTables("PivotTable1").PivotFields("Cars"). _
EnableMultiplePageItems = True
' Deselect all cars named Altima
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Cars")
.PivotItems(Altima).Visible = False
End With
This works fine. However, if I replace the hardcoded value of Altima with a string, I get the dreaded "Unable to get the PivotItems property of the PivotField class" error.
Here is the code that gives me an ERROR:
Dim mystring As String
mystring = Altima
' Make all report fields visible
ActiveSheet.PivotTables("PivotTable1").PivotFields("Cars").CurrentPage = _
"(All)"
' Allow the user to select multiple selections
ActiveSheet.PivotTables("PivotTable1").PivotFields("Cars"). _
EnableMultiplePageItems = True
' Deselect all cars that the string variable, mystring points to
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Cars")
.PivotItems(mystring).Visible = False
End With
What I had planned to do is loop through the above code and dynamically set the value of mystring to what I needed it to be, rather than set it to Altima, as I have in the above example.
I have seen lots of people post similar issues with getting the above error, but not really any posts that I came across that addresses the proper syntax for using variables in this code.
Can anyone help?
Regards,
Scotty81
What I really want to do is within a more complex pivot table, only show a few pivot items. I want to loop through all items and only make visible certain ones.
For simplicity, I've generated a VERY simple pivot table and put the names of cars in the report filter.
Here is the hardcoded code that DOES work:
' Make all report fields visible
ActiveSheet.PivotTables("PivotTable1").PivotFields("Cars").CurrentPage = _
"(All)"
' Allow the user to select multiple selections
ActiveSheet.PivotTables("PivotTable1").PivotFields("Cars"). _
EnableMultiplePageItems = True
' Deselect all cars named Altima
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Cars")
.PivotItems(Altima).Visible = False
End With
This works fine. However, if I replace the hardcoded value of Altima with a string, I get the dreaded "Unable to get the PivotItems property of the PivotField class" error.
Here is the code that gives me an ERROR:
Dim mystring As String
mystring = Altima
' Make all report fields visible
ActiveSheet.PivotTables("PivotTable1").PivotFields("Cars").CurrentPage = _
"(All)"
' Allow the user to select multiple selections
ActiveSheet.PivotTables("PivotTable1").PivotFields("Cars"). _
EnableMultiplePageItems = True
' Deselect all cars that the string variable, mystring points to
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Cars")
.PivotItems(mystring).Visible = False
End With
What I had planned to do is loop through the above code and dynamically set the value of mystring to what I needed it to be, rather than set it to Altima, as I have in the above example.
I have seen lots of people post similar issues with getting the above error, but not really any posts that I came across that addresses the proper syntax for using variables in this code.
Can anyone help?
Regards,
Scotty81