Changing Data Source for Pivot Tables

[Aaron DeYoung]

I have been working through this code and I cannot figure out where I am going wrong. It runs through the code but it doesn't change the Pivot Tables data source. I am not using a Table, rather a sheet range for my data btw.

[Sub AdjustAllPivotDataRanges()
'PURPOSE: Dynamically change every pivot table's data source range in the workbook




Dim sht As Worksheet
Dim pvt As PivotTable
Dim StartPoint As Range
Dim rng As Range
Dim SourceAddress As String


'Enter Worksheet Name that holds your Pivot data source
Set sht = ActiveWorkbook.Worksheets("Completed Projects")


'Enter first cell in your Pivot data source
Set StartPoint = sht.Range("A5")


'Create SourceData address
Set rng = sht.Range(StartPoint, StartPoint.SpecialCells(xlLastCell))
SourceAddress = sht.Name & "!" & rng.Address(ReferenceStyle:=xlR1C1)


'Loop through and update pivot tables with new data source range
For Each sht In ThisWorkbook.Worksheets
For Each pvt In sht.PivotTables

'Change Pivot Table's data source range address
pvt.ChangePivotCache _
ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=SourceAddress)

'Ensure Pivot Table is refreshed
pvt.RefreshTable

Next pvt
Next sht


'Completion Message
MsgBox "All Pivot Table Data Source Ranges have been updated in this workbook!", vbInformation


End Sub]