Search Contextures Sites

Contextures
Excel news
by email

 

 

 

 

 

 

 

 

 

Excel Pivot Table Data Source

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.

data source list

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).

list data source or mdx

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

More Pivot Table Resources

Tutorials:

 

 

Privacy Policy

 

Contextures Inc., Copyright 2014
All rights reserved.

 

Search Contextures Sites