Changing source range of pivot table

[cwb1021]

Good morning experts,

I'm trying to change the range of a pivot table to prevent recreating the entire table every time I run this procedure. I have found the following code on TheSpreadsheetGuru.com and have been attempting to modify it for my workbook. However, I'm getting a couple of errors that I do not understand.

Sub ChangePivotDataSourceRange()
'PURPOSE: Automatically readjust a Pivot Table's data source range
'SOURCE: www.TheSpreadsheetGuru.com/The-Code-Vault
Dim wsAS As Worksheet
Dim wsCharts 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 wsAS = Worksheets("AveragedStats")
  Set wsCharts = Worksheets("Charts")
'Enter in Pivot Table Name
  PivotName = "PivotTable1"
'Dynamically Retrieve Range Address of Data
  Set StartPoint = wsAS.Range("A1")
  Set DataRange = wsAS.Range(StartPoint, StartPoint.SpecialCells(xlLastCell))
  
  NewRange = wsAS.Name & "!" & DataRange.Address(ReferenceStyle:=xlR1C1)
'Debug.Print NewRange.Value
'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
  wsCharts.PivotTables(PivotName).ChangePivotCache _
    ThisWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=NewRange)
      
'Ensure Pivot Table is Refreshed
  wsCharts.PivotTables(PivotName).RefreshTable
'Complete Message
  MsgBox PivotName & "'s data source range has been successfully updated!"
End Sub

The first error I get is the message box telling me my column header is blank, although there are no blank cells in the range.

If I get past this, I would get the error: Run-time error '-2147024809 (80070057)': The pivottable field name is not value etc...

Is there a more simple way that I can update the source range of the pivot table? The source will always start on the same worksheet with columns A to K, but will have a variable number of rows.

I suppose that one way to do it would to always leave the source range the same size, but I would also like to avoid blank data showing in the pivot chart.

Any ideas or suggestions would be greatly appreciated.

Thanks,

Chris

[p45cal]

try this one-liner in your workbook:

Sub blah()
   Sheets("Charts").Range("A1").PivotTable.ChangePivotCache ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Sheets("AveragedStats").Range("A1").CurrentRegion)
End Sub

[cwb1021]

p45cal - You da man! Works perfectly. Thanks for the help.

-Chris