Pivot Table - Source Data Information

M

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
 
B

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Pivot table multiple filters 2
Used Range and Source data of a Pivot Table 1
Pivot Table Question 2
How to identify Chart Source data as range? 3
How do I change source data range for an existing Pivot Table? 1
Changing PivotTable Source programmatically 1
DYNAMIC RANGE FOR PIVOT TABLE 1
Code that will rerun or refresh a pivot table (after new data ispasted into the original Pivot Table 3

Top