updating pivot charts with vba

I have two worksheets, one that contains the pivot tables and another that
contains the pivot charts. I programmed on the pivot tables worksheet that
when a selection is made from the pivot table dropdown all the other pivot
table selections are updated automatically to the selection. What im looking
to do though is on the pivot chart page, when i change the selection there
how does it change all the pivot chart selections automatically? Btw just in
case someone wants to change several pivot tables at once, this code might
help out for that. The code im using to change the pivot tables is:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim pt As PivotTable
Dim pf As PivotField
Dim pt1 As PivotTable
Dim pf1 As PivotField
Dim str As String
Dim counter As Integer
counter = 1

' Base table that gets updated
' PivotTable2 is the main table that is updated.
Set pt = Me.PivotTables("PivotTable2")
Set pf = pt.PivotFields("str_seg")

' User can't select the (All) selection
If pf.CurrentPage = "(All)" Then
pf.CurrentPage = "Nation"
MsgBox ("The display of (All) is disabled.")
End If

' 10 pivot tables on the work sheet, go through each one of them and update
the selections
While (counter <= 10)
Set pt1 = Me.PivotTables(counter)
Set pf1 = pt1.PivotFields("str_seg")
str = pf.CurrentPage
pf1.CurrentPage = str
counter = counter + 1
Wend

End Sub

I found out how to do it, sort of. What i did was on the chart it self,
select the chart title and in the formula box make a cell reference to the
pivot table selection area. This will update each time a new selection
occurs, though the rest of the next that i need to come out needs to be put
into another text box. For some reason i cant do =C1 & "TEXT" as it says
that is an invalid function. I also tried concatenate(C1, "Text") and it
still didnt work, anyone have a solution to this?

Tim

"Tim" wrote:

> I have two worksheets, one that contains the pivot tables and another that
> contains the pivot charts. I programmed on the pivot tables worksheet that
> when a selection is made from the pivot table dropdown all the other pivot
> table selections are updated automatically to the selection. What im looking
> to do though is on the pivot chart page, when i change the selection there
> how does it change all the pivot chart selections automatically? Btw just in
> case someone wants to change several pivot tables at once, this code might
> help out for that. The code im using to change the pivot tables is:
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> Dim pt As PivotTable
> Dim pf As PivotField
> Dim pt1 As PivotTable
> Dim pf1 As PivotField
> Dim str As String
> Dim counter As Integer
> counter = 1
>
> ' Base table that gets updated
> ' PivotTable2 is the main table that is updated.
> Set pt = Me.PivotTables("PivotTable2")
> Set pf = pt.PivotFields("str_seg")
>
> ' User can't select the (All) selection
> If pf.CurrentPage = "(All)" Then
> pf.CurrentPage = "Nation"
> MsgBox ("The display of (All) is disabled.")
> End If
>
> ' 10 pivot tables on the work sheet, go through each one of them and update
> the selections
> While (counter <= 10)
> Set pt1 = Me.PivotTables(counter)
> Set pf1 = pt1.PivotFields("str_seg")
> str = pf.CurrentPage
> pf1.CurrentPage = str
> counter = counter + 1
> Wend
>
> End Sub

The text box, or chart title, can only link to a cell -- the text box
can't contain a formula.

So, instead of linking to the selection cell, link to another cell that
contains the formula: =C1 & " Text"

Tim wrote:
> I found out how to do it, sort of. What i did was on the chart it self,
> select the chart title and in the formula box make a cell reference to the
> pivot table selection area. This will update each time a new selection
> occurs, though the rest of the next that i need to come out needs to be put
> into another text box. For some reason i cant do =C1 & "TEXT" as it says
> that is an invalid function. I also tried concatenate(C1, "Text") and it
> still didnt work, anyone have a solution to this?
>
> Tim
>
> "Tim" wrote:
>
>
>>I have two worksheets, one that contains the pivot tables and another that
>>contains the pivot charts. I programmed on the pivot tables worksheet that
>>when a selection is made from the pivot table dropdown all the other pivot
>>table selections are updated automatically to the selection. What im looking
>>to do though is on the pivot chart page, when i change the selection there
>>how does it change all the pivot chart selections automatically? Btw just in
>>case someone wants to change several pivot tables at once, this code might
>>help out for that. The code im using to change the pivot tables is:
>>
>>Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>>Dim pt As PivotTable
>>Dim pf As PivotField
>>Dim pt1 As PivotTable
>>Dim pf1 As PivotField
>>Dim str As String
>>Dim counter As Integer
>>counter = 1
>>
>>' Base table that gets updated
>>' PivotTable2 is the main table that is updated.
>>Set pt = Me.PivotTables("PivotTable2")
>>Set pf = pt.PivotFields("str_seg")
>>
>>' User can't select the (All) selection
>>If pf.CurrentPage = "(All)" Then
>> pf.CurrentPage = "Nation"
>> MsgBox ("The display of (All) is disabled.")
>>End If
>>
>>' 10 pivot tables on the work sheet, go through each one of them and update
>>the selections
>>While (counter <= 10)
>>Set pt1 = Me.PivotTables(counter)
>>Set pf1 = pt1.PivotFields("str_seg")
>>str = pf.CurrentPage
>>pf1.CurrentPage = str
>>counter = counter + 1
>>Wend
>>
>>End Sub
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html