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

I am new to VBA and need to change the data source of the pivot to 'Sheet1'!$Q$4:$W$1940. The pivot table is present on sheet1 Y1. Please help with a working code, I have been searching on google but no luck!

Thanks in advance!

CodePudding user response：

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

CodePudding user response：

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