Use 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.
Last updated: April 23, 2021 11:56 AM