Run-time Error 5: Invalid Procedure - Changing Pivot Table Source

JD728

New Member
Joined
Apr 24, 2019
Messages
9
Hi,
I'm getting the "Run-time error '5': Invalid Procedure call or argument at the line below in blue: PT1.ChangePivotCache ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PTSourceRngFcst, Version:=6)

These are the values of the cells listed in the code:
G24 = \\hguwarps03\WarrenData\Finance\MANAGEMENT_REPORTS\Reforecast 2019\P06_19-Jun Reforecast\
G25 = fncl-analysis-data_May-29.xlsx
G26 = L:\Finance\MANAGEMENT_REPORTS\Reforecast 2019\P06_19-Jun Reforecast\[fncl-analysis-data_May-29.xlsx]fncl-analysis-data

Any help you can provide would be greatly appreciated. Thanks!!!

Rich (BB code):
Sub Update_Sources()
'
Dim wb As Workbook, wbFromFcst As Workbook, wbFromReFcst As Workbook
Dim wkshtSourceFcst As Worksheet, wkshtSourceReFcst As Worksheet
Dim fromPathFcst As String
Dim SourceNameFcst As String
Dim PTSourcePathFcst As String
Dim rng As Range
Dim StartPointFcst As Range
Dim PTSourceRngFcst As String
Dim PT1 As PivotTable


Set wb = ThisWorkbook


fromPathFcst = Sheets("CONTROLS").Range("G24")
SourceNameFcst = Sheets("CONTROLS").Range("G25")
PTSourcePathFcst = Sheets("CONTROLS").Range("G26")


Set wbFromFcst = Workbooks.Open(fromPathFcst & SourceNameFcst)
Set wkshtSourceFcst = wbFromFcst.Sheets("fncl-analysis-data")
Set StartPointFcst = wkshtSourceFcst.Range("A1")
Set rng = wkshtSourceFcst.Range(StartPointFcst, StartPointFcst.SpecialCells(xlLastCell))
PTSourceRngFcst = PTSourcePathFcst & "!" & rng.Address(ReferenceStyle:=xlR1C1)


Set PT1 = ThisWorkbook.Sheets("Pivots").PivotTables("Customer_Cat_LocnType")


PT1.ChangePivotCache ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PTSourceRngFcst, Version:=6)








End Sub
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
PTSourceRngFcst should be a range reference, not a string.
 
Upvote 0
Thanks Norie. I changed it to Range, however now I get the Run-time error '91': Object Variable or With block variable not set at the line: PTSourceRngFcst = PTSourcePathFcst & "!" & rng.Address(ReferenceStyle:=xlR1C1)

What am I doing wrong?

Rich (BB code):
Sub Update_Sources()
'
Dim wb As Workbook, wbFromFcst As Workbook, wbFromReFcst As Workbook
Dim wkshtSourceFcst As Worksheet, wkshtSourceReFcst As Worksheet
Dim fromPathFcst As String
Dim SourceNameFcst As String
Dim PTSourcePathFcst As String
Dim rng As Range
Dim StartPointFcst As Range
Dim PTSourceRngFcst As Range
Dim PT1 As PivotTable


Set wb = ThisWorkbook


fromPathFcst = Sheets("CONTROLS").Range("G24")
SourceNameFcst = Sheets("CONTROLS").Range("G25")
PTSourcePathFcst = Sheets("CONTROLS").Range("G26")


Set wbFromFcst = Workbooks.Open(fromPathFcst & SourceNameFcst)
Set wkshtSourceFcst = wbFromFcst.Sheets("fncl-analysis-data")
Set StartPointFcst = wkshtSourceFcst.Range("A1")
Set rng = wkshtSourceFcst.Range(StartPointFcst, StartPointFcst.SpecialCells(xlLastCell))
PTSourceRngFcst = PTSourcePathFcst & "!" & rng.Address(ReferenceStyle:=xlR1C1)


Set PT1 = ThisWorkbook.Sheets("Pivots").PivotTables("Customer_Cat_LocnType")


PT1.ChangePivotCache ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PTSourceRngFcst, Version:=6)








End Sub


PTSourceRngFcst should be a range reference, not a string.
 
Upvote 0
Thanks Norie. I changed it to Range, however now I get the Run-time error '91': Object Variable or With block variable not set at the line: PTSourceRngFcst = PTSourcePathFcst & "!" & rng.Address(ReferenceStyle:=xlR1C1)

What am I doing wrong?
 
Upvote 0
Same problem, this will return a string.
Code:
PTSourcePathFcst & "!" & rng.Address(ReferenceStyle:=xlR1C1)

Is rng a reference to the range you want to create the pivot cache from?
 
Upvote 0
Same problem, this will return a string.
Code:
PTSourcePathFcst & "!" & rng.Address(ReferenceStyle:=xlR1C1)

Is rng a reference to the range you want to create the pivot cache from?

Yes. It's the full path and array for the SourceData for the Pivot Table.
 
Upvote 0
I'm basically trying to define the path and range of cells for the SourceData when trying to change the source of the Pivot Table. Thanks for your help!
 
Upvote 0
Is the source workbook open?

Is rng an object variable that refers to the source range for the pivot cache?

If it is, does this work?
Code:
PT1.ChangePivotCache ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rng, Version:=6)
 
Upvote 0
Is the source workbook open?

Is rng an object variable that refers to the source range for the pivot cache?

If it is, does this work?
Code:
PT1.ChangePivotCache ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rng, Version:=6)


Yes, the source workbook is open. Rng is a Range variable that sets the range of the source file. Can I still use the code you provided?
 
Upvote 0
If rng refers to the range that you want to use for the pivot cache that's probably what you should be using, why not give it a try?:)
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

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