How to change the data source of a PIVOT table Using VBA?

Change Pivot Table Data Source

Option Explicit

Sub ChangeDataSource() '
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Worksheets("Sheet1")
    Dim pCell As Range: Set pCell = ws.Range("Y3")
    Dim ptbl As PivotTable: Set ptbl = pCell.PivotTable
    ptbl.ChangePivotCache wb.PivotCaches.Create(SourceType:=xlDatabase, _
        SourceData:=ws.Range("Q4:W1940"), Version:=7)
End Sub

I assume that since you are trying to set the range with code, you won't always know the # of rows.

But - you have to know something about the data to ensure it won't get errors. I am assuming that you know there will be data in cell Q4, and in your data set - there will always be a value in column Q.

Sub test()
    Dim intLastRow As Integer, intLastColumn As Integer
    
    intLastRow = Sheet1.Range("Q" & Application.Rows.Count).End(xlUp).Row
    intLastColumn = Sheet1.Cells(4, Application.Columns.Count).End(xlToLeft).Column
    
    If intLastRow <= 5 Then intLastRow = 5 'need to ensure there's a range to be slected
    If intLastColumn <= Sheet1.Range("Q4").Column Then intLastColumn = Sheet1.Range("Q4").Column 'Ensure there's at least one column to select
    
    Sheet1.PivotTables("pvtMyData").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase _
            , SourceData:=Sheet1.Range(Sheet1.Range("Q4"), Sheet1.Cells(intLastRow, intLastColumn)) _
            , Version:=8)
End Sub

** note - when you click in a pivot table and then click on the "Pivot Table Analyze" Ribbon, there is an option at the top-left to name your pivot table. In the example, I assume the name is "pvtMyData" - you can use the default name - e.g. "PivotTable5" but it might get confusing.

This will list all sources for all pivot tables in your entire workbook.

Sub PivotSourceListAll()
Dim wb As Workbook
Dim ws As Worksheet
Dim wsList As Worksheet
Dim pt As PivotTable
Dim lPT As Long
Dim wsPT As Worksheet
Dim PTCount As Long
Dim strSD As String
On Error Resume Next

Set wb = ActiveWorkbook

For Each wsPT In wb.Sheets
  If wsPT.PivotTables.Count Then
    PTCount = PTCount + 1
  End If
  If PTCount > 0 Then Exit For
Next wsPT

If PTCount = 0 Then
  MsgBox "No pivot tables in this workbook"
  Exit Sub
End If

Set wsList = Worksheets.Add
With wsList
  .Range(.Cells(1, 1), .Cells(1, 3)).Value _
      = Array("Sheet", _
        "PivotTable", "Source Data")
End With
lPT = 2

For Each ws In wb.Worksheets
  For Each pt In ws.PivotTables
    strSD = pt.SourceData
    If strSD = "" Then strSD = "N/A"
    With wsList
      .Range(.Cells(lPT, 1), _
        .Cells(lPT, 3)).Value _
          = Array(ws.Name, pt.Name, strSD)
    End With
    lPT = lPT + 1
    strSD = ""
  Next pt
Next ws

With wsList
  .Columns("A:C").EntireColumn.AutoFit
  .Rows(1).Font.Bold = True
End With

End Sub

Get that working, and it should take much, then you should easily be able to change the source/range of your pivot table to a different source/range.

https://www.contextures.com/excelpivottabledatasource.html