Pivot Table - Change Fields Items then run this code.

[ianb]

Can any one finish this program please.


Sub Chart2()


'If pivot table field is changed then do this


Sheets("Dashboard (Individual)").Select

ActiveSheet.ChartObjects("1").Activate

ActiveChart.ChartTitle.Select

Selection.Characters.Text = Application.WorksheetFunction.Text(Range("H60").Value, "dd/mm/yyyy")


'refresh chart


End Sub


Also I have this program which sets the pivot table date each day H60 also shows the Date =$I$81 (I81 is the pivot table Date)


When a run a script I loose my Chart Title being =H60 therefore I need a script to enter this in again.


ActiveSheet.PivotTables("PivotTable2").PivotFields("Resolved Date"). _

CurrentPage = Format(Now, "dd/mm/yyyy")

Application.GoTo Range("a1")

 

[ianb]

OR...


ANy way to explain then I run this the chart titles loose the formula to be equal to a cell ref number.


Dim Fnt As String

Dim FntSz As Double

Dim FntR As Integer

Dim FntG As Integer

Dim FntB As Integer


Fnt = "Rockwell (Body)" 'Set Font type

FntSz = 14 'Set Font Size


'Black Text


FntR = 0 'Set Font Color Red

FntG = 0 'Set Font Color Green

FntB = 0 'Set Font Color Blue


For Each ws In Worksheets

For Each ch In ws.ChartObjects


ch.Activate

ch.Chart.ChartTitle.Select

Selection.Format.TextFrame2.TextRange.Font.Name = Fnt

Selection.Format.TextFrame2.TextRange.Font.Size = FntSz

With Selection.Format.TextFrame2.TextRange.Font.Fill

.ForeColor.RGB = RGB(FntR, FntG, FntB)

.Transparency = 0

.Solid

Application.GoTo Range("a1")

End With

Next ch

Next ws