List all Source Data in Pivot Tables

[AlexPDEBMan]

Hi

Is there a way I can list all of the source data in all of the Pivot tables in a workbook?

The reason I ask is that I have some source data that I don't think is used anywhere and I just want to delete it. Experience has taught me not to be so rash and I just wanted to ensure that it is not being used by this particular spreadsheet that has about 40 different pivot tables.

I guess I could write some VBA code to loop through each worksheet, then through each pivot table and write the source data location to a text stream.

There may be an Excel function I don't know about though! Can someone help?

 

[gaj104]

Using VBA as mentioned, how about:

Sub PTCount()

Dim pt As PivotTable
Dim s As Worksheet, ws As Worksheet
Dim i As Long

Set ws = ThisWorkbook.Sheets.Add(ThisWorkbook.Sheets(1))
ws.Activate

ws.Range("A1:C1").Value = Array("Sheet", "PivotName", "DataSource")

i = 2

For Each s In ThisWorkbook.Sheets

    For Each pt In s.PivotTables
    
        ws.Cells(i, 1).Value = s.Name
        ws.Cells(i, 2).Value = pt.Name
        ws.Cells(i, 3).Value = pt.SourceData
        i = i + 1
        
    Next pt

Next s

ws.Columns.AutoFit

End Sub

 

[AlexPDEBMan]

Hi gaj104

Thanks for this. I had to ammend the code slightly but the Pivot table code you gave me was invaluable. Cheers for your help on this.

 

[chinmayh]

Thanks a ton for this.
Source data format is in RC format, trying to get that in the normal format

 

[Legacy 3234]

This was exactly what I needed to make sure I changed the sources in a whole bunch of pivot tables correctly. Thank you.