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!
23 Answers
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 SubI 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 SubGet 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.
3
