VBA to Change SourceData Based on Cell Value - Invalid Procedure Call or Argument

JD728

New Member
Joined
Apr 24, 2019
Messages
9
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"
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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Forum statistics

Threads
1,223,264
Messages
6,171,081
Members
452,377
Latest member
bradfordsam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top