Create a list of sheet name and pivot table names in xls file 2

[MSIsam]

Is there a way to extract a list that shows the sheet name and the pivot table name for all of the pivot tables in a workbook?

 

[Fenrirshowl]

Looks like you need some VBA. Start with:

Sub pvt()
Dim pv As PivotTable
For Each sht In ActiveWorkbook.Sheets
    For Each pv In sht.PivotTables
            MsgBox pv.Name
    Next pv
Next sht
End Sub

 

[Fenrirshowl]

sorry, cut off there.

That's the basic code to list them in message boxes, you can define a the cells and use a counter to list them somewhere specific on your spreadsheet.

something like

Sub pvt()
Dim pv As PivotTable
counter=0
For Each sht In ActiveWorkbook.Sheets
    For Each pv In sht.PivotTables
            sheets(1).cells(2+counter,1)=sht.name
            sheets(1).cells(2+counter,2)=pv.name
            counter=counter+1
    Next pv
Next sht
End Sub

 

[MSIsam]

Thanks Fenrirshowl,

This is close. I would like the final output to be a list that would show up on the spreadsheet of all the tab names and pivot table names. I am not very proficient with VBA and the only thing I could add was the active sheet name, which is not the true location of the Pivot.

Sub pvt()
Dim pv As PivotTable
For Each sht In ActiveWorkbook.Sheets
    For Each pv In sht.PivotTables
            MsgBox ActiveSheet.Name & " " & pv.Name
    Next pv
Next sht
End Sub

Thanks,
Sam

 

[MSIsam]

Hi Fenrirshowl,
I tried to run the code that you supplied and nothing happens. Am I missing something?

Thanks,
Sam

 

[DirkStruan]

Sam

All the code appears to work for me - have you checked the cells from A2 onwards in your first sheet?

(Naturally you'll need to make sure A2 downwards on your first sheet is empty or else you will overwrite your data - suggest adding a new sheet at the start of the workbook.)

D

 

[MSIsam]

Looks Like I was missing something. This is perfect. Thanks so much to the both of you!

 

[DirkStruan]

No problem - didn't do a lot for that star so you may want to amend the code to:

Sub pvt()
Dim pv As PivotTable

Sheets.Add
cells(1,1)="Sheet name"
cells(1,2)="Pivot table name"
ActiveSheet.Move before:=Sheets(1)

counter=0

For Each sht In ActiveWorkbook.Sheets
    For Each pv In sht.PivotTables
            sheets(1).cells(2+counter,1)=sht.name
            sheets(1).cells(2+counter,2)=pv.name
            counter=counter+1
    Next pv
Next sht
End Sub

This way it adds a sheet for you and puts in the titles. Saves chancing overwriting data if someone changes the sheet order.

D