Pivot Table Data Ranges

D

Darby

Is there any nifty way to find all the Data Ranges referred to in all the
Pivot tables in a file.

I needed to change the ranges referred to and want to make sure I got them
all.

Thanks
 
T

Tom Hutchins

This macro lists the source data range(s) for every pivot table in the active
workbook on a new sheet:

Sub ListAllSourceData()
'List source data ranges in all pivot tables on a new sheet
On Error Resume Next
Application.ScreenUpdating = False
Dim NewWS As Worksheet, StartWS As Worksheet
Dim pt As PivotTable
Dim ws As Worksheet
Dim ii As Long, jj As Long
Dim sdArray
Set StartWS = ActiveSheet
'Add a new worksheet to the current workbook at the end.
Worksheets.Add.Move after:=Sheets(Sheets.Count)
Set NewWS = ActiveSheet
jj = 0
'Look at every pivot table on every sheet
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
sdArray = pt.SourceData
'If a multiple consolidation range pivot table,
'SourceData is an array.
If IsArray(sdArray) Then
For ii = LBound(sdArray) To UBound(sdArray)
jj = jj + 1
NewWS.Cells(jj, 1) = pt.Name
NewWS.Cells(jj, 2) = sdArray(ii)
Next ii
Else
jj = jj + 1
NewWS.Cells(jj, 1) = pt.Name
NewWS.Cells(jj, 2) = pt.SourceData
End If
Next pt
Next ws
StartWS.Activate
'Free object variables
Set StartWS = Nothing
Set NewWS = Nothing
Application.ScreenUpdating = True
End Sub

Paste the code in a VBA module in your workbook. To run it from a worksheet,
press Alt-F8 (or select Tools >> Macro >> Macros). Select ListAllSourceData,
then click Run.

If you are new to macros, this link to Jon Peltier's site may be helpful:
http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-elses-macro/

Hope this helps,

Hutch
 
Top