updating pivot charts with vba

[Guest]

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

 

[Guest]

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

 

[Debra Dalgleish]

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"