CODEWITHSUNDEEP
vbaexcelexcel-formula
Jojo Acharya
Jojo Acharya

Reputation: 71

Run-time error '5': Invalid procedure call or argument while running macro

I am using a V.B.A Code to Dynamically Change a Pivot Table's Data Source Range. The sheet containing the pivot table is in a different sheet(Sheet7) (in the same excel file) and the data source is in Sheet2 and the macro runs in the Sheet9 i.e a different sheet that the data source and the pivot table. Upon running the code throws error : Run-time error '5': Invalid procedure call or argument (Note : All the sheets are of the same excel file)

The code is as follows:

Sub UPDATE_BUTTON()
'PURPOSE: Automatically readjust a Pivot Table's data source range
'SOURCE: www.TheSpreadsheetGuru.com/The-Code-Vault

Dim Data_sht As Worksheet
Dim Pivot_sht As Worksheet
Dim StartPoint As Range
Dim DataRange As Range
Dim PivotName As String
Dim NewRange As String

'Set Variables Equal to Data Sheet and Pivot Sheet
  Set Data_sht = ThisWorkbook.Worksheets("Sheet2")
  Set Pivot_sht = ThisWorkbook.Worksheets("Sheet7")

'Enter in Pivot Table Name
  PivotName = "A"

'Dynamically Retrieve Range Address of Data
  Set StartPoint = Data_sht.Range("A1")
  Set DataRange = Data_sht.Range(StartPoint, StartPoint.SpecialCells(xlLastCell))

  NewRange = Data_sht.Name & "!" & _
    DataRange.Address(ReferenceStyle:=xlR1C1)

'Make sure every column in data set has a heading and is not blank (error prevention)
  If WorksheetFunction.CountBlank(DataRange.Rows(1)) > 0 Then
    MsgBox "One of your data columns has a blank heading." & vbNewLine _
      & "Please fix and re-run!.", vbCritical, "Column Heading Missing!"
    Exit Sub
  End If
'DataArea = "Sheet7!R1C1:R" & Selection.Rows.Count & "C" & Selection.Columns.Count
'Change Pivot Table Data Source Range Address
  Pivot_sht.PivotTables(PivotName).ChangePivotCache _
    ThisWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=NewRange)

'Ensure Pivot Table is Refreshed
  Pivot_sht.PivotTables(PivotName).RefreshTable

'Complete Message
  MsgBox PivotName & "'s data source range has been successfully updated!"

End Sub

The same is throwing an error in the line : 

Pivot_sht.PivotTables(PivotName).ChangePivotCache _
    ThisWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=NewRange)

If I change the same to :

ThisWorkbook.Worksheets("Sheet7").PivotCaches.Create( _

It gives Run-time error '438': Object doesnt support this property or method

Upvotes: 0

Views: 609

Answers (1)

zero
zero

Reputation: 17

PivotCaches is tied to workbook, not worksheet. https://msdn.microsoft.com/en-us/vba/excel-vba/articles/pivotcaches-object-excel

Upvotes: 1

Related Questions