Pivot Table - Source Data Information

[MSweetG222]

Can anyone help me with the vba code needed to determine a pivot table's
source data broken down into its individual components?

1. Full Directory Path
2. Full Workbook Name
3. Sheet Name
4. Range Address

 

[Barb Reinhardt]

I'm not sure I think this is the BEST way, but it works.

Option Explicit

Sub FindPivot()

Dim myPivot As Excel.PivotTable
Dim mySourceData As String
Dim mySheetName As String
Dim myRangeAddress As String
Dim myVal As Long
Dim myWS As Excel.Worksheet
Dim myWB As Excel.Workbook
Dim mySelection As Excel.Range
Dim myWBPath As String
Dim myWBName As String
Dim myWSName As String
Dim aWS As Excel.Worksheet
Dim myAddress As String

Set aWS = ActiveSheet
Set mySelection = Selection
Application.ScreenUpdating = False

For Each myPivot In ActiveSheet.PivotTables
Debug.Print myPivot.Name, myPivot.SourceData
mySourceData = myPivot.SourceData
myVal = InStr(mySourceData, "!")
If myVal > 0 Then
Application.Goto (mySourceData)
myRangeAddress = Selection.Address
Set myWS = Selection.Parent
Set myWB = myWS.Parent
myWBPath = myWB.FullName
myWBName = myWB.Name
myWSName = myWS.Name
Debug.Print myPivot.Name, myWBPath, myWBName, myWSName, myRangeAddress


End If
Next myPivot

aWS.Select
Application.ScreenUpdating = True

End Sub

HTH,
Barb Reinhardt

 

[MSweetG222]

Barb.

Thanks for your help. I will give it a try.

MSweetG222