Button to change pivot table sources

Hi there,

I've got a very basic understanding of VBA.

My goal is to create a button that will do the following:

Prompt a pop-up to for data source for all pivot tables in the file. (Source is usually external file sitting on NAS)

If opted 'cancel' than do nothing if new source selected refresh all pivot tables as well.

All pivots share the same cache. So is the only single slicer linked to all pivot tables and as I understand needs to be disconnected and then reconnected.

I tried to replicate other posts but to no avail. Definitely know that I'm missing the slicer connecting and disconnecting parts.

Many thanks in advanced.

Option Explicit


Private Sub Data Source_Click()

Sub ChangeSourceData()

Dim Wks As Worksheet
Dim PT As PivotTable
Dim UserRange As Range
Dim SourceData As String
Dim CacheIndex As Long
Dim Cnt As Long

On Error Resume Next
Set UserRange = Application.InputBox( _
Prompt:="Select the range for the source data.", _
Title:="Select a range", _
Type:=8)
On Error GoTo 0

If UserRange Is Nothing Then Exit Sub

SourceData = Application.ConvertFormula(UserRange.Address(, , , True), xlA1, xlR1C1)

Cnt = 0
For Each Wks In ActiveWorkbook.Worksheets
For Each PT In Wks.PivotTables
Cnt = Cnt + 1
If Cnt = 1 Then
PT.ChangePivotCache ThisWorkbook.PivotCaches.Create(xlDatabase, SourceData)
CacheIndex = PT.CacheIndex
Else
PT.CacheIndex = CacheIndex
End If
Next PT
Next Wks

End Sub

Bump. Bump. Bump.