What are the cells in a Pivot Table range?
 

I have a pivot table where the field button for the data is located in cell
A3. I noticed that when Excel automatically creates a chart along with the
table it says the location is at Range("A3").

How can I through VB find out the cells that are contained in that range so
that I can set the format correctly to either "currency" or "percent" rather
than "general"?

What are the cells in a Pivot Table range?
 

You can use the TableRange2 property to get the address (including the
page fields):

ActiveSheet.PivotTables(1).TableRange2.Address

Or use the TableRange1 property to get the address without the page fields:

ActiveSheet.PivotTables(1).TableRange1.Address


Darrell Wesley wrote:
I have a pivot table where the field button for the data is located in cell
A3. I noticed that when Excel automatically creates a chart along with the
table it says the location is at Range("A3").

How can I through VB find out the cells that are contained in that range so
that I can set the format correctly to either "currency" or "percent" rather
than "general"?


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

What are the cells in a Pivot Table range?
 

Thanks Debra,

This didn't eactly give me what I needed but through use of the MID$ and
INSTR functions I was able to get what I needed.

Darrell

"Debra Dalgleish" wrote:

You can use the TableRange2 property to get the address (including the
page fields):

ActiveSheet.PivotTables(1).TableRange2.Address

Or use the TableRange1 property to get the address without the page fields:

ActiveSheet.PivotTables(1).TableRange1.Address


Darrell Wesley wrote:
I have a pivot table where the field button for the data is located in cell
A3. I noticed that when Excel automatically creates a chart along with the
table it says the location is at Range("A3").

How can I through VB find out the cells that are contained in that range so
that I can set the format correctly to either "currency" or "percent" rather
than "general"?


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

What are the cells in a Pivot Table range?
 

You're welcome. I'm glad you were able to get the result you needed.

Darrell Wesley wrote:
Thanks Debra,

This didn't eactly give me what I needed but through use of the MID$ and
INSTR functions I was able to get what I needed.

Darrell

"Debra Dalgleish" wrote:


You can use the TableRange2 property to get the address (including the
page fields):

ActiveSheet.PivotTables(1).TableRange2.Address

Or use the TableRange1 property to get the address without the page fields:

ActiveSheet.PivotTables(1).TableRange1.Address


Darrell Wesley wrote:

I have a pivot table where the field button for the data is located in cell
A3. I noticed that when Excel automatically creates a chart along with the
table it says the location is at Range("A3").

How can I through VB find out the cells that are contained in that range so
that I can set the format correctly to either "currency" or "percent" rather
than "general"?


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html