Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic pivot table selection
Please bear with me, I'm sure I don't have the right lingo, but I'll give it
my best shot. I have a pivot table with page items. I want to be able to write a macro that will dynamically set a "page" item in the pivot table to what the user selects from a list on a data validation cell. So if the user selects Monday in cell A1, I want the pivot table for that page item item to change to "Monday". I know how to write code to change pivot items for a value that is predermined. ie I always want the page item to be Monday. But have never made the code dynamic and interactive with a value in a cell. Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic pivot table selection
Hi mcarter,
Sound like you already know how to set the PT's pagefield with VBA, and you just want to some how make that code to run when a user change a value in another cell. If this is the case, then there is not much work required to solve your problem. Let assume you have a macro called UpdatePTPageField() which will update the PT to a predefined value. And cell A1 with a list of validation data that the user use to select one of the choice. Sub UpdatePTPageField() 'Code to update the PT page field to "MONDAY" End Sub Now You should modify the UpdatePTPageField() to accept a parameter (value of cell A1, and use this parameter to update the PT. Sub UpdatePTPageField(PageFieldItem As String) ' Code to update the PT page field to PageFieldItem End Sub Next, you need to setup the worksheet to detect changes in cells A1. To do this, go to the code view of the worksheet and select "Worksheet_Change" event. Private Sub Worksheet_change(ByVal Target As Range) Dim ws As WorkSheet Set ws = ActiveSheet If (Target.Row = 1 And Target.Column = 1) Then ' Code to do thing here if the Cell "A1" changes UpdatePTPagefield(Range("A1")) End If End Sub Hong Quach "mcarter" wrote: Please bear with me, I'm sure I don't have the right lingo, but I'll give it my best shot. I have a pivot table with page items. I want to be able to write a macro that will dynamically set a "page" item in the pivot table to what the user selects from a list on a data validation cell. So if the user selects Monday in cell A1, I want the pivot table for that page item item to change to "Monday". I know how to write code to change pivot items for a value that is predermined. ie I always want the page item to be Monday. But have never made the code dynamic and interactive with a value in a cell. Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic pivot table linked to dynamic excel spreadsheets | Excel Worksheet Functions | |||
Pivot table from dynamic refreshed pivot table | Excel Worksheet Functions | |||
Code for a Macro that allows a Pivot table to be repeatedly run (fora dynamic rows in a table, but static field names) | Excel Programming | |||
Dynamic pivot table | Excel Programming | |||
Pivot Table - Multiple Pivot Field Selection | Excel Programming |