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 adds a new worksheet to the workbook, and lists all the pivot tables, their sheet names, and their data sources.

Sub PivotSourceListAll()
Dim wb As Workbook
Dim ws As Worksheet
Dim wsList As Worksheet
Dim pt As PivotTable
Dim lPT As Long
On Error Resume Next

Set wb = ActiveWorkbook
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
    With wsList
      .Range(.Cells(lPT, 1), .Cells(lPT, 3)).Value _
          = Array(ws.Name, pt.Name, pt.SourceData)
    End With
    lPT = lPT + 1
  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 adds a sheet to the workbook, with a list of pivot tables, and their data source (non-OLAP), or MDX query (OLAP-based).

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

On Error Resume Next

Set wb = ActiveWorkbook
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

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
  .Rows(1).Font.Bold = True
End With

End Sub

Change Data Source for All Pivot Tables

If you want to change the data source for all the pivot tables in a workbook, you can use the following code. It adds a sheet to the workbook, with a list of named ranges. Then, it prompts you to enter one of those names, as the new data source for all the pivot tables.

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.

Sub PivotSourceChangeAll()
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

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!

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