Search Contextures Sites

Contextures
Excel news
by email

We respect your email privacy

 

 

 

 

 

 

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

 

 

Pivot Table Tutorials

Pivot Table Introduction 
Clear Old Items in Pivot Table
Create a Pivot Table in Excel 2007 
Custom Calculations 
Data Field Layout
Dynamic Data Source
FAQs - Pivot Tables
Field Settings
Filter Source Data  
Filters, Top 10 
Filters, Report Filters
GetPivotData Function
Grand Totals
Grouping Data
Layout, Excel 2007
Multiple Consolidation Ranges
Pivot Cache   
PivotTable Style
Printing a Pivot Table   
Protection  
Running Totals  
Show and Hide Items 
Sorting
Subtotals 
Summary Functions
Unique Item Count

Pivot Table Books

Beginning Pivot Tables (Excel 2007) 
Pivot Tables Recipe Book (Excel 2003) 
Pivot Tables Recipe Book (Excel 2007) 

Pivot Table Add-Ins

Pivot Power 
Pivot Play PLUS 

Pivot Table Videos

Clear Old Items
Copy a Custom PivotTable Style
Create Pivot Table in Excel 2007
Create Pivot Table from Multiple Sheets
Data Field Layout
Date Filters, Add
GetPivotData
Group Data
Layout, Excel 2007
Report Filters, Add
Running Totals
Select Sections
Subtotals, Create Multiple
Top 10 Filters

       Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact

 

Privacy Policy

 

Contextures Inc., Copyright ©2013
All rights reserved.

 

Search Contextures Sites