Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot table data source | Excel Discussion (Misc queries) | |||
Code that will rerun or refresh a pivot table (after new data ispasted into the original Pivot Table's Source Range) | Excel Programming | |||
Pivot Table Data Source | Excel Discussion (Misc queries) | |||
Pivot table data source | Excel Discussion (Misc queries) | |||
Pivot Table data source "data source contains no visible tables" | Excel Worksheet Functions |