Set selection of Pivot Table using VBA

[Flooder4]

Hi all,

I have to manipulate the selection of fields in a Pivot Table using
VBA.
Description:
One Worksheet hosts a Pivot Table which hosts a lot of Data. Another
Worksheet, which is used for an Executive Summary only displays one
line of the original table.
To allow the user switching between different Quarters in this
executive summary I have to change the selected data in the original
Pivot Table.
I created a drop down field where Quarters (Q1, Q2, Q3, Q4) can be
selected and would like that a change here would affect the original
Pivot Table (change the Page Field Data).
Therefore I programmed a small macro which doesn't work in the way I
expect it. After all it does nothing.
Can anybody help me as I am a bloody starter in programming VBA?

Public Sub ChangePage()
Sheets("OverallView").Select
Set
ActiveSheet.PivotTables("PivotTable3").PivotFields("Quarter").CurrentPage
= Range("ExecutiveS!G5").Value
Sheets("ExecutiveS").Select
End Sub

 

[Charles Chickering]

The Range object in VBA does not behave like you are in excel. You must first
give it the worksheet object then the range you are after.
Worksheets("ExecutiveS").Range("G5")

HTH

 

[Flooder4]

Thanks alot!

My code looks now like this:

Public Sub ChangePage()
Sheets("OverallView").Select

ActiveSheet.PivotTables("PivotTable3").PivotFields("Quarter").CurrentPage
= Worksheets("ExecutiveS").Range("G5")
Sheets("ExecutiveS").Select
End Sub

Now I get the error: Run-time error 1004. Application defined or
object defined error.

Can somebody help me?

 

[Charles Chickering]

I just tried it myself, it appears that the Pivot Table only wants to deal
with strings so you'll have to typecast (or force) the range value into a str:
CurrentPage= CStr(Worksheets("ExecutiveS").Range("G5"))

Let me know if you have any more problems.

 

[Flooder4]

Hi Charles,

your posting helped me very much!
Now everything works flawlessly, but now I tried to change also the
term, not only the quarter.
Term can only take values "1", "2", "(All)".
It works without any problem if you choose 2 or (All), but if you
choose 1, I get the error message "Unable to set the _Default property
of the PivotItem class".

My code now looks like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("OverallView").Select

ActiveSheet.PivotTables("PivotTable3").PivotFields("Quarter").CurrentPage
= CStr(Worksheets("ExecutiveS").Range("F4"))

ActiveSheet.PivotTables("PivotTable3").PivotFields("Term").CurrentPage
= CStr(Worksheets("ExecutiveS").Range("F5"))
Sheets("ExecutiveS").Select
End Sub

You think you can help me again?

 

[Charles Chickering]

I'm having difficulty repeating your problem. Can you send a sample workbook
to (e-mail address removed) I'll take a look at it and see if I can spot the
problem.

 

[Flooder4]

Charles, thanks for your offer but due to compliance guidelines I
cannot send you example data. But I found out what the reason for this
runtime error could be.
After I formated the source data of the pivot table as Number instead
of Text the worksheet worked fine.
After all I can not unterstand the reason for the problem, but I try
to get around by formating the source data as number by VBA.
Thank you very much for your help!
Stefan