How to derive source sheet name of Pivot Table

[ThrottleWorks]

Hi,

Is it possible to derive sheet name of the pivot table it is linked to.

For example, I have pivot table in Sheet2, pivot range data is in Shee1 of the same file.
If I have to check which sheet is linked to this pivot I will do manually ‘Options -> Change Data Source -> Change Data Source’.

I will copy path populated in browsing window and paste in an excel cell to get the sheet name.

In above mentioned example Pivot path is ‘Sheet1!$A$1:$B$6’. How do I achieve this in VBA. Can anyone please help me in this.


Hope, sample file is not required for this issue. Please let me know if you want sample file. I will try to upload.

 

[Deepak]

Check it

http://dailydoseofexcel.com/archives/2010/11/04/getting-the-source-range-of-a-pivot-table-with-vba/

Option Explicit

Public Function GetRangeFromSourceData(pt As PivotTable) As String
   
    Dim lBang As Long
    Dim lClose As Long
    Dim sAddress As String
    Dim sSheet As String
    Dim sSource As String
    Dim rReturn As Range
   
    Const sBANG As String = "!"
    Const sCLOSE As String = "]"
   
    sSource = Application.ConvertFormula(pt.SourceData, xlR1C1, xlA1)
   
    lBang = InStr(1, sSource, sBANG)
    lClose = InStr(1, sSource, sCLOSE)
   
    If lBang > 0 Then
        sAddress = Mid$(sSource, lBang + 1, Len(sSource))
        If lClose > 0 Then
            sSheet = Mid$(sSource, lClose + 1, lBang - lClose - 1)
        Else
            sSheet = Left$(sSource, lBang - 1)
        End If
        Set rReturn = pt.Parent.Parent.Sheets(sSheet).Range(sAddress)
    Else
        Set rReturn = pt.Parent.Range(sSource)
    End If
   
    GetRangeFromSourceData = rReturn.Address(, , , True)
   
End Function

Sub test()
    Debug.Print GetRangeFromSourceData(Sheet2.PivotTables("PivotTable1"))
End Sub

 

[Deepak]

Or for Just sheet name!

Change this line!

GetRangeFromSourceData = rReturn.Parent.Name

 

[ThrottleWorks]

Hi @Deepak sir, thanks a lot for the help. I am checking this. Will revert with details. Have a nice week ahead.

 

[ThrottleWorks]

Hi @Deepak sir, it is working nice. Thanks a lot.

Also, would like to share code for counting number of columns in a 'pivot table' (if you do not mind) found on Google. Good night.

http://www.mrexcel.com/forum/excel-questions/624007-pivot-table-row-column-count.html

NoColsSFInPivot1 := Excel.ActiveSheet.PivotTables(PivotTableName).TableRange2.Columns.Count