Pivot Table Macros in VBA Won't Work.

Posted by Craig Tanner on February 12, 2001 8:56 PM

I have been unable to change the PivotItems or PivotFields using VBA. Even if I record the Macros and try to replay them I get an error message like:

"Unable to se the Visible property of the PivotItem class"

"Unable to get the PivotTables Property of the Worksheet class"

Any Ideas on how to get this to work? I'm using Excel 97.

An example of a portion of code that won't work is:

ActiveSheet.PivotTables("PivotTable4").PivotFields("Itemid").CurrentPage = "TN1846"

Thanks in advance.

Craig

Posted by Dave Hawley on February 12, 2001 9:54 PM

Hi Craig

Unless you want to use PivotSelect then use this way.

Dim Pt As PivotTable
Set Pt = ActiveSheet.PivotTables("PivotTable4")
With Pt
.PivotFields("Itemid").CurrentPage = "TN1846"
End With

This is a much cleaner way than PivotSelect

Hope this helps

Dave

OzGrid Business Applications

Posted by Craig on February 13, 2001 7:42 PM

Dave,

Thanks for the code. I agree that it is much cleaner. However, I still get the dreaded "Run-Time error" '1004' "unable to set the _Default property of the PivotItem class"

Any ideas on what else could be wrong?

Craig

Posted by Dave Hawley on February 13, 2001 9:19 PM

Craig which line causes the error ?

Try: .PageFields("Itemid").CurrentPage = "TN1846"

Are you sure you have a Pivot called "PivotTable4" with a Pagefield called "Itemid" and it does contain "TN1846" ?

Dave

OzGrid Business Applications