caoliz
04-13-2017, 12:16 PM
I am trying to be able to automatically update the data source range for my pivot table without manually selecting the data. I found the below code, but still receive this error: Run-time error "1004': Method "PivotTables of object "_Worksheet' failed.
Can anyone help? I am very new to VBA and do not understand where this error is coming from.
Sub AdjustPivotDataRange()
'PURPOSE: Automatically readjust a Pivot Table's data source range
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("Claims Filed Data")
Set Pivot_sht = ThisWorkbook.Worksheets("Pivots")
'Enter in Pivot Table Name
PivotName = "Claims Filed Per Day"
'Dynamically Retrieve Range Address of Data
Set StartPoint = Data_sht.Range("A3")
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
'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
Thank you!
Can anyone help? I am very new to VBA and do not understand where this error is coming from.
Sub AdjustPivotDataRange()
'PURPOSE: Automatically readjust a Pivot Table's data source range
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("Claims Filed Data")
Set Pivot_sht = ThisWorkbook.Worksheets("Pivots")
'Enter in Pivot Table Name
PivotName = "Claims Filed Per Day"
'Dynamically Retrieve Range Address of Data
Set StartPoint = Data_sht.Range("A3")
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
'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
Thank you!