Skip to content

Instantly share code, notes, and snippets.

@drewchapin
Created September 28, 2016 13:19
Show Gist options
  • Save drewchapin/c3884eee2ac9f17c86d6f6e99a5d7e8f to your computer and use it in GitHub Desktop.
Save drewchapin/c3884eee2ac9f17c86d6f6e99a5d7e8f to your computer and use it in GitHub Desktop.
Replace pivot table source paths with current path.
Attribute VB_Name = "Module1"
Public Sub Update_Pivot_Tables()
On Error GoTo Exception
Dim sheet As Worksheet, table As PivotTable
For Each sheet In ThisWorkbook.Sheets
For Each table In sheet.PivotTables
Dim newSource As String, sourceType As XlPivotTableSourceType, version As Variant, i As Long
i = InStr(1, StrReverse(table.SourceData), "\", vbTextCompare)
If i <= 0 Then
newSource = table.SourceData
Else
newSource = "'" & ThisWorkbook.Path & "\" & Right(table.SourceData, i - 1)
End If
sourceType = ThisWorkbook.PivotCaches(table.CacheIndex).sourceType
version = ThisWorkbook.PivotCaches(table.CacheIndex).version
Debug.Print table.SourceData & " -> " & newSource
sheet.PivotTables(table.Name).ChangePivotCache ThisWorkbook.PivotCaches.Create(xlDatabase, newSource, version)
Next
Next
Debug.Print "Done!"
Exit Sub
Exception:
Dim Result As Variant
Result = MsgBox("Error: " & Err.Number & vbCrLf & vbCrLf & Err.Description, vbCritical Or vbOKCancel)
Select Case Result
Case vbOK
Resume Next
Case vbCancel
Debug.Print "Cancelled."
Exit Sub
End Select
End Sub
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment