Is activecell in a Table, a Pivot Table, or a data range?

[jomili]

Trying to come up what a way to determine what my activecell is in; a Table, a PivotTable, a data range, or a shape. Whatever it's in will determine what my macro does next. I'm not finding much online except https://www.techrepublic.com/blog/mi...is-in-a-table/, which had a function that gave me hope, but doesn't seem to work. Any help would be appreciated.

[mjr veverka]

Attach a sample xls file with objects to the location of the active cell in them (minimum: 2 x Tables, 2 x PivotTables, 2 x Shapes, 2 x free/normal lists)

[romperstomper]

You can use activecell.pivottable or activecell.listobject. I don’t know what you mean by a data range, and cells are never in shapes (they are different layers).

[jomili]

Shape isn't really necessary, I just threw that in. Let me explain more of what I need this more, maybe that will clarify.

Every morning a new Active Position Report (APR) is created by another team. Approx 15K lines by 185 columns of information. Each day I have to make reports from subsets of the data, and I do that via 3 different macros, but I want to do it with one. Here's why there's 3:

The APR on some days is just the data residing in cells (Normal Range), plain vanilla. I run my macro, it creates my reports, life is good.
The APR on some days is in a table format. My second macro converts the Table to a Normal Range then creates my reports.
The APR on some days in in a pivot. My third macro converts the pivot to values and cleans it up, which results in a Normal Range, then creates my reports.

So Tables and Pivot's need to be converted first. I can use an inputbox to tell VBA which of the three types it is, but I'd rather have VBA determine that based on the ActiveCell, hence this post.

[jomili]

I'm part way there, by cheating. The sub below tells me whether the Activesheet has a pivot, or a table, or neither (normal range). I can use this for my immediate need, because only one of the three will exist in my APR, but I'd still like to see if there's a way to determine this from the Activecell.

Please Login or Register  to view this content.

[romperstomper]

You'd basically just do something like this:

Please Login or Register  to view this content.

[jomili]

Much Better! I tested it as shown below, and it passed with flying colors. Now I can adapt it to my specific need. Thanks so much for helping me with this!

Please Login or Register  to view this content.

[mjr veverka]

Use 3 "For Each Next" loops dedicated to individual objects ('ListObjects', 'PivotTables', 'Shapes') and 'CurrentRegion' and use the command syntax assigned to the methods and properties of these individual objects and the 'Intersect' method, which will identify the affiliation (or lack thereof) of 'ActiveCell' to the given object. Use such elements as: 'Address', 'Name', 'RangeSelection', 'TopLeftCell', 'BottomRightCell' ... maybe 'Split'.