Pivot Table Data Source Macros

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.

List Pivot Table Data Sources

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

1. On the Excel Ribbon, click the View tab
2. At the far right, click Macros
3. Select this macro in the list, and click the Run button

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

List Pivot Table Data Source or MDX

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

1. On the Excel Ribbon, click the View tab
2. At the far right, click Macros
3. Select this macro in the list, and click the Run button

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

Change Data Source - Named Range

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

1. On the Excel Ribbon, click the View tab
2. At the far right, click Macros
3. Select this macro in the list, and click the Run button

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

Change Data Source - Named Table

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

1. On the Excel Ribbon, click the View tab
2. At the far right, click Macros
3. Select this macro in the list, and click the Run button

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

Pivot Table Tools

To save time when building, formatting and modifying your pivot tables, use the tools in my Pivot Power Premium add-in. With just a few clicks, you can:

• copy the formatting from one pivot table, and apply it to another pivot table.
• change all the values from Count to Sum
• remove the "Sum of" from all the headings

and much more!

Download the Sample File

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.

More Tutorials

Fix Pivot Table Source Data

Unpivot Excel Data Macro

Pivot Table Layout VBA

FAQs - Pivot Tables

Pivot Table Intro

Summary Functions

Clear Old Items in Pivot Table