Very often our data will change from sat 100 items to 700 to 300 to 1000. This gives us a problem with the Pivots.
I found the below code on the web to dynamically change a pivot table but it does not work as it throws out an error saying that "one of the data columns has a blank heading" but in fact non of them are blank.
Is it possible an Expert out there could check out the code for me please, or provide me with VBA code that would do what I am looking to do.
I did try to contact the owner but I am unable to do so.
'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("S
Set Pivot_sht = ThisWorkbook.Worksheets("S
'Enter in Pivot Table Name
PivotName = "PivotTable1"
'Dynamically Retrieve Range Address of Data
Set StartPoint = Data_sht.Range("A1")
Set DataRange = Data_sht.Range(StartPoint,
NewRange = Data_sht.Name & "!" & _
'Make sure every column in data set has a heading and is not blank (error prevention)
) > 0 Then
MsgBox "One of your data columns has a blank heading." & vbNewLine _
& "Please fix and re-run!.", vbCritical, "Column Heading Missing!"
'Change Pivot Table Data Source Range Address
'Ensure Pivot Table is Refreshed
MsgBox PivotName & "'s data source range has been successfully updated!"