|
Home > Pivot > Create > Macros Pivot Table Data Source MacrosUse these Excel macros to list or change pivot table data sources. For a macro to unpivot the source data, go to the Unpivot Macro page NOTE: If you want manually locate and change the source data, go to the pivot table source data page.
|
If you have multiple pivot tables and lists in an Excel file, you might need to identify which data source each pivot table uses.
The following code checks for at least one pivot table in the workbook. If there aren't any pivot tables, the macro stops. Otherwise, the macro adds a new worksheet to the workbook, and lists all the pivot tables, their sheet names, and their data sources.
NOTE: If the source data is not found, for example pivot tables added to the Data Model, the Source Data shows as "N/A".

Add this code to a regular code module in your workbook, and to run the code, follow these steps
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
If your workbook includes OLAP-based pivot tables, you can show the MDX query, instead of the Data Source.
The following code checks for at least one pivot table in the workbook. If there aren't any pivot tables, the macro stops. Otherwise, the macro adds a new worksheet to the workbook, with a list of pivot tables, and their data source (non-OLAP), or MDX query (OLAP-based).

Add this code to a regular code module in your workbook, and to run the code, follow these steps
Sub PivotSourceListAllWithMDX()
Dim wb As Workbook
Dim ws As Worksheet
Dim wsList As Worksheet
Dim pt As PivotTable
Dim lPT As Long
Dim strMDX As String
Dim strSource As String
Dim wMax As Long
Dim wsPT As Worksheet
Dim PTCount As Long
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, 4)).Value _
= Array("Sheet", "PivotTable", _
"Source Data", "MDX Query")
End With
lPT = 2
wMax = 50
For Each ws In wb.Worksheets
For Each pt In ws.PivotTables
If pt.PivotCache.OLAP = False Then
strSource = pt.SourceData
strMDX = ""
Else
strSource = "OLAP"
strMDX = pt.MDX
End If
With wsList
.Range(.Cells(lPT, 1), _
.Cells(lPT, 4)).Value _
= Array(ws.Name, pt.Name, _
strSource, strMDX)
End With
lPT = lPT + 1
Next pt
Next ws
With wsList
.Columns("A:D").EntireColumn.AutoFit
.Columns("A:D").VerticalAlignment = xlTop
.Rows(1).Font.Bold = True
With .Columns(4)
If .ColumnWidth > wMax Then
.ColumnWidth = wMax
End If
.WrapText = True
End With
End With
End Sub
If you want to change the data source for all the pivot tables in a workbook, based on a named range, you can use the following code. This could be used, for example, if you have changed the original data source into an Excel Table, and you want to use a named range that is based on the new table.
The macro adds a sheet to the workbook, with a list of named ranges that are in the workbook. Next, the macro prompts you to enter one of those names, to use as the new data source for all the pivot tables.
NOTE: This macro will change the source data for normal pivot tables only. It cannot change OLAP-based pivot tables, such as those that are added to the Data Model.

Add this code to a regular code module in your workbook, and to run the code, follow these steps
Sub PivotSourceChangeAll_Ranges()
'for normal pivot tables only
'not for OLAP-based (e.g. Data Model)
'lists all named ranges
Dim wb As Workbook
Dim ws As Worksheet
Dim wsList As Worksheet
Dim pt As PivotTable
Dim strSD As String
Dim strMsg As String
On Error Resume Next
Application.DisplayAlerts = False
Application.EnableEvents = False
Set wb = ActiveWorkbook
Set wsList = Worksheets.Add
With wsList
.Range("A1").ListNames
.Columns(2).ClearContents
.Columns(1).EntireColumn.AutoFit
End With
strMsg = "Enter one of the Source Data Range Names "
strMsg = strMsg & vbCrLf & "from list shown on worksheet"
strSD = InputBox(Prompt:=strMsg, Title:="Source Data")
If strSD = "" Then
MsgBox "Cancelled"
Exit Sub
Else
For Each ws In wb.Worksheets
For Each pt In ws.PivotTables
pt.ChangePivotCache _
wb.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=strSD)
Next pt
Next ws
End If
exit_Handler:
wsList.Delete
Application.EnableEvents = True
Application.DisplayAlerts = True
Exit Sub
err_Handler:
MsgBox "Could not update pivot table source data"
Resume exit_Handler
End Sub
If you want to change the data source for all the pivot tables in a workbook, based on a named Excel table, you can use the following code.
The macro adds a sheet to the workbook, with a list of named Excel Tables that are in the workbook. Next, the macro prompts you to enter one of those table names, to use as the new data source for all the pivot tables.
NOTE: This macro will change the source data for normal pivot tables only. It cannot change OLAP-based pivot tables, such as those that are added to the Data Model.

Add this code to a regular code module in your workbook, and to run the code, follow these steps
Sub PivotSourceChangeAll_Tables()
'for normal pivot tables only
'not for OLAP-based (e.g. Data Model)
'lists all tables
Dim wb As Workbook
Dim ws As Worksheet
Dim wsT As Worksheet
Dim wsList As Worksheet
Dim pt As PivotTable
Dim tbl As ListObject
Dim strSD As String
Dim strMsg As String
Dim nm As Name
Dim lRow As Long
Dim TableCount As Long
On Error Resume Next
Application.DisplayAlerts = False
Application.EnableEvents = False
Set wb = ActiveWorkbook
For Each wsT In wb.Sheets
If wsT.ListObjects.Count Then
TableCount = TableCount + 1
End If
If TableCount > 0 Then Exit For
Next wsT
If TableCount = 0 Then
MsgBox "No named tables in this workbook"
GoTo exit_Handler
End If
Set wsList = Worksheets.Add
With wsList
.Range("A1").Value = "Named Tables"
.Range("B1").Value = "Sheet"
.Range("C1").Value = "Address"
lRow = 2
For Each wsT In wb.Sheets
For Each tbl In wsT.ListObjects
.Range(.Cells(lRow, 1), .Cells(lRow, 3)).Value _
= Array(tbl.Name, wsT.Name, tbl.Range.Address)
lRow = lRow + 1
Next tbl
Next wsT
.Columns("A:C").EntireColumn.AutoFit
End With
strMsg = "Enter one of the Table Names "
strMsg = strMsg & vbCrLf & "from list shown on worksheet"
strSD = InputBox(Prompt:=strMsg, Title:="Source Data")
If strSD = "" Then
MsgBox "Cancelled"
Exit Sub
Else
For Each ws In wb.Worksheets
For Each pt In ws.PivotTables
If pt.PivotCache.OLAP = False Then
pt.ChangePivotCache _
wb.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=strSD)
End If
Next pt
Next ws
End If
exit_Handler:
wsList.Delete
Application.EnableEvents = True
Application.DisplayAlerts = True
Exit Sub
err_Handler:
MsgBox "Could not update pivot table source data"
Resume exit_Handler
End Sub
Download the sample file for this tutorial. The zipped workbook is in xlsm format, and contains the macros from this page. Be sure to enable macros when you open the workbook, if you want to test the macros.
Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Sign Up.
Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!
Last updated: February 16, 2025 7:31 PM