Hi,
I'm having the Debug error:Invalid Procedure Call or Argument for the following code. I'm trying to change the source (excel file saved on a public drive) of pivot table. In the "CONTROLS" tab I have in B3 the path and range of the source. The Debug error happens at the point where I try to set the SourceData = to the "PTSource" variable. Can you please help? Thanks!
CONTROLS Tab:
cell B1: \\hguwarps03\WarrenData\Finance\MANAGEMENT_REPORTS\Reforecast 2019\P05_19-May Reforecast\Margin Investigation\
cell B3: ="L:"&RIGHT($B$1,LEN($B$1)-SEARCH("\finance",$B$1))&"[fncl-analysis-data_wResearchComments.xlsx]fncl-analysis-data!C1:C104"
I'm having the Debug error:Invalid Procedure Call or Argument for the following code. I'm trying to change the source (excel file saved on a public drive) of pivot table. In the "CONTROLS" tab I have in B3 the path and range of the source. The Debug error happens at the point where I try to set the SourceData = to the "PTSource" variable. Can you please help? Thanks!
CONTROLS Tab:
cell B1: \\hguwarps03\WarrenData\Finance\MANAGEMENT_REPORTS\Reforecast 2019\P05_19-May Reforecast\Margin Investigation\
cell B3: ="L:"&RIGHT($B$1,LEN($B$1)-SEARCH("\finance",$B$1))&"[fncl-analysis-data_wResearchComments.xlsx]fncl-analysis-data!C1:C104"
result: L:\Finance\MANAGEMENT_REPORTS\Reforecast 2019\P05_19-May Reforecast\Margin Investigation\[fncl-analysis-data_wResearchComments.xlsx]fncl-analysis-data!C1:C104
Code:
Sub Update_Source()
'
Dim wb As Workbook, wbFrom As Workbook
Dim fromPath As String
Dim PTSource As String
fromPath = Sheets("CONTROLS").Range("B1")
PTSource = Sheets("CONTROLS").Range("B3")
If Right(fromPath, 1) <> "\" Then fromPath = fromPath & "\"
Set wb = ThisWorkbook
Set wkbFrom = Workbooks.Open(fromPath & "fncl-analysis-data_wResearchComments.xlsx")
Dim ws As Worksheet, pt As PivotTable
For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
PTSource, Version:=6)
Next
Next
End Sub