Pages

Thursday, March 24, 2011

Find Pivot Name for all Pivot Charts in workbook

If you want to know the pivot name and source data of all pivotcharts in a workbook

Here is the code:-


Sub checksource()
Dim x As PivotTable
Dim s As Charts
Dim y As ChartObject
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
For Each y In ws.ChartObjects
MsgBox "Chart Name:- " & y.Name & vbNewLine & _
"Pivot Name:- " & y.Chart.PivotLayout.PivotTable.Name & vbNewLine & _
"Pivot Source Sheet Name:- " & Left(y.Chart.PivotLayout.PivotTable.SourceData, InStr(1, y.Chart.PivotLayout.PivotTable.SourceData, "!R") - 1) & vbNewLine & _
"Pivot Source:- " & y.Chart.PivotLayout.PivotTable.SourceData

Next y
Next ws
End Sub

No comments:

Post a Comment