VBA to change Data Source in Pivot Table - Getting invalid procedure call or argument

parkerbelt

Active Member
Joined
May 23, 2014
Messages
377
I'm trying to use VBA to change the data source for 3 pivot tables that I have and I'm getting Invalid procedure call or argument at this bit of code:

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

Here is the code that I'm using:
Code:
'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 PivotName2 As String
    Dim PivotName3 As String
    Dim PivotName4 As String
    Dim NewRange As String


    'Set Variables Equal to Data Sheet and Pivot Sheet
'        Set Data_sht = ThisWorkbook.Worksheets("Sheet1")
'        Set Pivot_sht = ThisWorkbook.Worksheets("Sheet2")
        Set Data_sht = Sheets("DATA DUMP")
'        Set Data_sht = ThisWorkbook.Worksheets("DATA DUMP")
        Set Pivot_sht = Sheets("PTs")


    'Enter in Pivot Table Name
'        PivotName = "PivotTable1"
        PivotName2 = "PivotTable2"
        PivotName3 = "PivotTable3"
        PivotName4 = "PivotTable4"
        
        
    'Dynamically Retrieve Range Address of Data
'        Set StartPoint = Data_sht.Range("A1")
        Set StartPoint = Data_sht.Range("A25")
        'Set DataRange = Data_sht.Range(StartPoint, StartPoint.SpecialCells(xlLastCell))
        Set DataRange = StartPoint.CurrentRegion
  
  
        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(25)) > 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


    'Change Pivot Table Data Source Range Address
     'Pivot Table 2
        Pivot_sht.PivotTables(PivotName2).ChangePivotCache _
            ThisWorkbook.PivotCaches.Create( _
            SourceType:=xlDatabase, _
            SourceData:=NewRange)
      'Pivot Table 3
        Pivot_sht.PivotTables(PivotName3).ChangePivotCache _
            ThisWorkbook.PivotCaches.Create( _
            SourceType:=xlDatabase, _
            SourceData:=NewRange)
      'Pivot Table 4
        Pivot_sht.PivotTables(PivotName4).ChangePivotCache _
            ThisWorkbook.PivotCaches.Create( _
            SourceType:=xlDatabase, _
            SourceData:=NewRange)
            
            
            
    'Ensure Pivot Table is Refreshed
      'Pivot Table 2
        Pivot_sht.PivotTables(PivotName2).RefreshTable
      'Pivot Table 3
        Pivot_sht.PivotTables(PivotName3).RefreshTable
      'Pivot Table 4
        Pivot_sht.PivotTables(PivotName4).RefreshTable
        
        
    'Complete Message
        MsgBox PivotName2 & "'s data source range has been successfully updated!"
        MsgBox PivotName3 & "'s data source range has been successfully updated!"
        MsgBox PivotName4 & "'s data source range has been successfully updated!"
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
I figured out a solution that required way less code. Here's what I used:
Code:
Sheets("DATA DUMP").Select
    ActiveSheet.ShowAllData
 
    Range("A26").Select
    
    Selection.CurrentRegion.Select


    DataArea = "DATA DUMP!R25C1:R" & Selection.Rows.Count & "C" & Selection.Columns.Count
 
    
    Sheets("PTs").Select
    Range("D5").Select
    
    
    ActiveSheet.PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:=DataArea, _
        Version:=xlPivotTableVersion14)
 
Upvote 0

Forum statistics

Threads
1,223,275
Messages
6,171,121
Members
452,381
Latest member
Nova88

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