Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot table auto field selection
Hi
I have a number of pivot tables that have filters to be slected by the user, but I want to auto select the value using field values held in another worksheet, Currrently when the user selects a value form the other worksheet (using a drop down box) various calculations are done and this prompts teh users to select certain values in the other worksheets. I have tried the follwoing code which sometimes works but at other times it doesn't and being a complete VBA novice I don't know what I'm doing wrong Sheets("Contract totals").Select ActiveSheeet.PivotTables("Shares_select").Pivotfie lds ("Netreins").CurrentPage.Name = Range("X2") It doesn't seem to like the Range("X2") which is a field on the spreadsheet that is linked to a value on the source worksheet. I would rahter it point to the actual other worksheet file but I don't know how to achive that. Anyone any ideas? regards John |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot table auto field selection
Assume the sheet with the pivottable is named PTbleSheetname and the sheet
with the source data is named SourceSheetName. Then you could do: Worksheets("PTbleSheetName").PivotTables( _ "Shares_select").Pivotfields _ ("Netreins").CurrentPage.Name = _ Worksheets("SourceSheetName").Range("X2").Value -- Regards, Tom Ogilvy "JohnC" wrote: Hi I have a number of pivot tables that have filters to be slected by the user, but I want to auto select the value using field values held in another worksheet, Currrently when the user selects a value form the other worksheet (using a drop down box) various calculations are done and this prompts teh users to select certain values in the other worksheets. I have tried the follwoing code which sometimes works but at other times it doesn't and being a complete VBA novice I don't know what I'm doing wrong Sheets("Contract totals").Select ActiveSheeet.PivotTables("Shares_select").Pivotfie lds ("Netreins").CurrentPage.Name = Range("X2") It doesn't seem to like the Range("X2") which is a field on the spreadsheet that is linked to a value on the source worksheet. I would rahter it point to the actual other worksheet file but I don't know how to achive that. Anyone any ideas? regards John |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot table auto field selection
Hii Tom
Many thanks for your reply. I have used your code and it is now working although hI have made a slight amendemnt to your code. I have removed the .Name after the CurrentPage as I ws getting a 'Run time error 1004 'Unable to set the Name property of the PivotItem class I don't know why but found the answer by trial and error....hopefully! One other question for anyone. Is it possible to test that the value the PivotField is being changed to actually exists in the listing without the macro bombing out? Regards John "Tom Ogilvy" wrote: Assume the sheet with the pivottable is named PTbleSheetname and the sheet with the source data is named SourceSheetName. Then you could do: Worksheets("PTbleSheetName").PivotTables( _ "Shares_select").Pivotfields _ ("Netreins").CurrentPage.Name = _ Worksheets("SourceSheetName").Range("X2").Value -- Regards, Tom Ogilvy "JohnC" wrote: Hi I have a number of pivot tables that have filters to be slected by the user, but I want to auto select the value using field values held in another worksheet, Currrently when the user selects a value form the other worksheet (using a drop down box) various calculations are done and this prompts teh users to select certain values in the other worksheets. I have tried the follwoing code which sometimes works but at other times it doesn't and being a complete VBA novice I don't know what I'm doing wrong Sheets("Contract totals").Select ActiveSheeet.PivotTables("Shares_select").Pivotfie lds ("Netreins").CurrentPage.Name = Range("X2") It doesn't seem to like the Range("X2") which is a field on the spreadsheet that is linked to a value on the source worksheet. I would rahter it point to the actual other worksheet file but I don't know how to achive that. Anyone any ideas? regards John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table field selection order | Excel Discussion (Misc queries) | |||
Synchronize the Pivot table page field selection in 2 tables? | Excel Worksheet Functions | |||
Multiple Field Selection for Pivot table Values Section | Excel Worksheet Functions | |||
Selection of multiple values for pivot table field | Excel Discussion (Misc queries) | |||
Pivot Table - Multiple Pivot Field Selection | Excel Programming |