Error When Using Variables in VBA for Pivot Table Commands

[Scotty81]

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

 

[Norie]

I'm actually surprised the first code works, but if it does I think I know why.

There are 2 ways to refer to things like pivot items - name or index.

In your first example you use a variable called Altima, however you haven't actually defined that variable or given it a value.

So VBA gives it the value Null/0/Something like that.

When you use it in the code it's being interpreted as the index of the pivot item.

So PivotItems(Altima) is the equivalent of PivotItems(0), following me so far?

Anyway in the second code, and this is strange you have declared the variable myString and given it a value, the undeclared variable Altima.

So myString is likely to be an empty string and I doubt there's a pivot item whose name is just an empty string.

Anyway, apologies for the epic, try just simply adding quotes around the name.

myString= "Altima"

PS It might actually seem to be working for Altima because that's the first item which will have the index 0.

PPS You could avoid something like this somewhat by putting Option Explicit at the top of the code. Won't fix it but it'll tell you there's a problem.

 

[Scotty81]

Norie,

Sorry for any confusion. In the first example, I meant to indicate that the car, Altima, was a specific value from the report filter, and not a variable. Thus, I meant it to read:

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Cars")
.PivotItems("Altima").Visible = False
End With

In the 2nd example, what I really plan on doing is dynamically set the value of mystring to the car name in question. So, on first iteration, mystring would have the value of Altima, and then on the 2nd iteration, it might have the value of Focus, another car name in the report filter. I plan on looping the code to just display the car names of interest.

I do have Option Explicit in my code and it doesn't flag any errors. Also, the value of mystring is properly set to "Altima" in the first iteration above. I can post the xlsx file if that would help.

Regards,
Scotty81

 

[Norie]

Well I based that post totally on the code you posted.

If you want to post a link to the file.

 

[Scotty81]

Hi Norie,

I posted my file in:
http://www.box.net/download/account/f_578217312/0/Pivot+Table+Example.xlsm


The simple file shows 1) some sample data, 2) a pivot table and 3) some macro code in Module 1. The line of code that is currently in there works. If you comment that one out and uncomment the other one, the code will generate an error: Unable to get the PivotItems property of the PivotField class.

The particular snippet of code is listed below. The (slightly longer, but complete code) is in the attached file.

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Cars")
'        The line that works is below
        .PivotItems("Altima").Visible = False
'        The line that does NOT work is below
'        .PivotItems(mystring).Visible = False
    End With

Hopefully this simple example will result in an explanation of why I can't replace a hardcoded string with a variable, when setting which report filter items are to be unselected.

Many thanks.

- Scotty81

 

[RoryA]

I think you posted the private link to the file, not the public one.

 

[Scotty81]

Thanks rorya,

I think this is the public link.

http://www.box.net/shared/xdgpc1om5z

Regards,
Scotty81

 

[RoryA]

Your variable assignment is wrong - this line:

mystring = Altima

should be:

mystring = "Altima"

 

[Scotty81]

rorya,

That did the trick!

Many thanks.

Regards,
Scotty81

 

[Norie]

Eh, didn't I kind of suggest changing that in my first post.

I know it went on a bit but I was just trying to explain why it wasn't working with the 2nd code and why it might seem to work with the first.

Anyway, as long as it's sorted.