PDA

View Full Version : [SOLVED] Changing source range of pivot table



cwb1021
04-08-2017, 07:53 AM
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 (http://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
04-08-2017, 09:12 AM
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
04-08-2017, 10:07 AM
p45cal - You da man! Works perfectly. Thanks for the help.

-Chris