Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set selection of Pivot Table using VBA
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set selection of Pivot Table using VBA
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 -- Charles Chickering "A good example is twice the value of good advice." " wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set selection of Pivot Table using VBA
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set selection of Pivot Table using VBA
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. -- Charles Chickering "A good example is twice the value of good advice." " wrote: 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set selection of Pivot Table using VBA
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set selection of Pivot Table using VBA
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set selection of Pivot Table using VBA
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table Selection | Excel Discussion (Misc queries) | |||
Automate pivot table selection | Excel Discussion (Misc queries) | |||
pivot table selection | Excel Worksheet Functions | |||
Pivot Table - Multiple Pivot Field Selection | Excel Programming | |||
Pivot table selection | Excel Programming |