Determining name of the active pivot table 1

[ejsmith]

I'm trying to determine the name of the pivot table where the activecell is located - or if the active cell is not loacated in/on a pivot table.
The reason being that I have certain code that alters pivot tables and I'd like the code to work only on the active pivot table (we usually have several pivots on the same tab). So if the user has the cursor in a pivot the code will alter only the one they are working with. I have been able to identify whether or not the cursor is in a pivot - but I can't determine the name of that pivot...
I'd appreciate any suggestions!

 

[VBAjedi]

If you can point your code to a range of cells, try:

ActiveCell.CurrentRegion

which returns a range containing the activecell, extending left/right to the first blank column and up/down to the first blank row. It's a bit hard to describe. Try running:

ActiveCell.CurrentRegion.Select

to see what it does.

VBAjedi

 

[SkipVought]

hi

here's a function that returns the pt name

Function WhatPivotTable(ac As Range)
    For Each pt In ac.Parent.PivotTables
        Set rng = Application.Intersect(pt.TableRange1, ac.CurrentRegion)
        If Not rng Is Nothing Then
            WhatPivotTable = pt.Name
            Exit Function
        End If
    Next
End Function

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[VBAjedi]

Slick, Skip! I don't need that at the moment, but have a star from me anyway. . . if CurrentRegion doesn't work, that certainly should!

VBAjedi

 

[SkipVought]

VBA,

I use CurrentRegion in the Intersect method.

Thanx!

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[ejsmith]

Thanks guys!
I also found

ActiveCell.PivotTable.Name

which throws an error if you aren't in a pivot and returns the name if you are!
Thanks for your help!