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:
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!"