Pivot Table - Source Data Information
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 -- Thank you for your help. MSweetG222 |
Pivot Table - Source Data Information
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" wrote: 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 -- Thank you for your help. MSweetG222 |
Pivot Table - Source Data Information
Barb. Thanks for your help. I will give it a try. :) MSweetG222 "Barb Reinhardt" wrote: 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" wrote: 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 -- Thank you for your help. MSweetG222 |
All times are GMT +1. The time now is 01:35 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com