Change source of Pivot Table and Add Pivot Chart for all Tables in a specific worksheet

[ChrisWhite]

Dear all,

what I am trying to achieve is to run through all Pivot Tables in 2 specific sheets and

1) Update the sources of all Pivot Tables to a new file
2) Add a Pivot Chart to all pivot Tables

I create the code below which basically does it job, but it seems very complicated to me and it a bit buggy (sometimes it works, sometimes it doesnt).
Also, all the Pivot Tables create individual Caches, although the tables refer to two caches, only.

Do you have any improvement suggestions or Ideas how to resolve the "cache" issue?

Best,
Christian




Sub Update_Pivot_Sources()


Dim pt As PivotTable
Dim token As String
Dim newco As String
Dim Sheets(1 To 2) As String
Dim I As Integer
Dim input_location As String
Dim PtRange As Range
Dim objChart As Chart
Dim chtSeries As SeriesCollection




input_location = Application.ActiveWorkbook.Path

Sheets(1) = "-C - Finance und Ops"
Sheets(2) = "-C - Investment"

newco = InputBox("Input company name:")
token = InputBox("Input two-digit company token:")


For I = 1 To 2
Worksheets(token & Sheets(I)).Activate
For Each pt In ActiveSheet.PivotTables
If I = 1 Then
' MsgBox ActiveSheet.Name
' MsgBox pt.Name
ActiveSheet.PivotTables(pt.Name).ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
input_location & "\[Input_" & newco & ".xlsx]" & token & "-I - Financial and Ops KPIs!$A:$F")

Set PtRange = pt.TableRange1
Set objChart = ActiveSheet.Shapes.AddChart.Chart

With objChart
.SetSourceData PtRange
.ChartType = xlColumnClustered
.ShowAllFieldButtons = True
End With
Else
' MsgBox ActiveSheet.Name
ActiveSheet.PivotTables(pt.Name).ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
input_location & "\[Input_" & newco & ".xlsx]" & token & "-I - Investment!$A:$E")

Set PtRange = pt.TableRange1
Set objChart = ActiveSheet.Shapes.AddChart.Chart

With objChart
.SetSourceData PtRange
.ChartType = xlColumnClustered
.ShowAllFieldButtons = True
End With
End If
Next pt
Next I
End Sub