Pivot table from a pivot table
 

I'm trying to create a pivot table from an existing pivot table.

My pivot table is in the range of A1:N484. It's a list of clients and their
total assets. I've added column O which categorizes each client based on
their assets. Now I want a pivot table by category.

My code is:

Set PTCache = ActiveWorkbook.PivotCaches.Add(xlDatabase, _
Sheets("Households").Range("A2", "O483").Address)
Set PT = PTCache.CreatePivotTable(TableDestination:="", _
TableName:="AssetLevels")

But VBA gives me an error 1004 at the "Set PT" statement. PTCache is defined
as a PivotCache, and PT is a PivotTable. If it makes any difference, the
same variables were use to create the original pivot table.

I find VBA help frustrating, because when I lookup the Add method of
PivotCaches, it says the first parameter is the type of data and can be
xlConsolidation, xlDatabase, xlExternal, xlPivotTable or xlScenario. But it
doesn't say where each option applies. I know I don't have xlEternal, but it
might be one of the others. Is that my problem?

--
Thanks,
Fred
Please reply to newsgroup, not e-mail

Pivot table from a pivot table
 

Instead of creating another pivot table, you could calculate the
category in the data source table.

In the data source table, add a column in which you use SUMIF, or
SUMPRODUCT to total the assets for each customer. In another column, use
a VLOOKUP formula to calculate the category.

Add the category field to the pivot table.

Fred Smith wrote:
I'm trying to create a pivot table from an existing pivot table.

My pivot table is in the range of A1:N484. It's a list of clients and their
total assets. I've added column O which categorizes each client based on
their assets. Now I want a pivot table by category.

My code is:

Set PTCache = ActiveWorkbook.PivotCaches.Add(xlDatabase, _
Sheets("Households").Range("A2", "O483").Address)
Set PT = PTCache.CreatePivotTable(TableDestination:="", _
TableName:="AssetLevels")

But VBA gives me an error 1004 at the "Set PT" statement. PTCache is defined
as a PivotCache, and PT is a PivotTable. If it makes any difference, the
same variables were use to create the original pivot table.

I find VBA help frustrating, because when I lookup the Add method of
PivotCaches, it says the first parameter is the type of data and can be
xlConsolidation, xlDatabase, xlExternal, xlPivotTable or xlScenario. But it
doesn't say where each option applies. I know I don't have xlEternal, but it
might be one of the others. Is that my problem?



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

Pivot table from a pivot table
 

Thanks Debra,

I thought of that, and it would solve part of problem. But I'm really
looking for the number of clients in each category. In the source data
table, a client could have 6 different accounts. I could use your suggestion
to categorize them, but how do I count them? If I count in a pivot table
from the original data source, it's going to use 6 rather than 1. Is it
possible to count the unique entries in a data source?

--
Regards,
Fred
Please reply to newsgroup, not e-mail


"Debra Dalgleish" wrote in message
...
Instead of creating another pivot table, you could calculate the
category in the data source table.

In the data source table, add a column in which you use SUMIF, or
SUMPRODUCT to total the assets for each customer. In another column, use
a VLOOKUP formula to calculate the category.

Add the category field to the pivot table.

Fred Smith wrote:
I'm trying to create a pivot table from an existing pivot table.

My pivot table is in the range of A1:N484. It's a list of clients and
their
total assets. I've added column O which categorizes each client based on
their assets. Now I want a pivot table by category.

My code is:

Set PTCache = ActiveWorkbook.PivotCaches.Add(xlDatabase, _
Sheets("Households").Range("A2", "O483").Address)
Set PT = PTCache.CreatePivotTable(TableDestination:="", _
TableName:="AssetLevels")

But VBA gives me an error 1004 at the "Set PT" statement. PTCache is
defined
as a PivotCache, and PT is a PivotTable. If it makes any difference, the
same variables were use to create the original pivot table.

I find VBA help frustrating, because when I lookup the Add method of
PivotCaches, it says the first parameter is the type of data and can be
xlConsolidation, xlDatabase, xlExternal, xlPivotTable or xlScenario. But
it
doesn't say where each option applies. I know I don't have xlEternal,
but it
might be one of the others. Is that my problem?



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

Pivot table from a pivot table
 

=SUMPRODUCT(1/COUNTIF(A1:A15,A1:A15))

if there are no empty cells in the specified. range.

--
Regards,
Tom Ogilvy


Fred Smith wrote in message
...
Thanks Debra,

I thought of that, and it would solve part of problem. But I'm really
looking for the number of clients in each category. In the source data
table, a client could have 6 different accounts. I could use your
suggestion
to categorize them, but how do I count them? If I count in a pivot table
from the original data source, it's going to use 6 rather than 1. Is it
possible to count the unique entries in a data source?

--
Regards,
Fred
Please reply to newsgroup, not e-mail


"Debra Dalgleish" wrote in message
...
Instead of creating another pivot table, you could calculate the
category in the data source table.

In the data source table, add a column in which you use SUMIF, or
SUMPRODUCT to total the assets for each customer. In another column, use
a VLOOKUP formula to calculate the category.

Add the category field to the pivot table.

Fred Smith wrote:
I'm trying to create a pivot table from an existing pivot table.

My pivot table is in the range of A1:N484. It's a list of clients and
their
total assets. I've added column O which categorizes each client based
on
their assets. Now I want a pivot table by category.

My code is:

Set PTCache = ActiveWorkbook.PivotCaches.Add(xlDatabase, _
Sheets("Households").Range("A2", "O483").Address)
Set PT = PTCache.CreatePivotTable(TableDestination:="", _
TableName:="AssetLevels")

But VBA gives me an error 1004 at the "Set PT" statement. PTCache is
defined
as a PivotCache, and PT is a PivotTable. If it makes any difference,
the
same variables were use to create the original pivot table.

I find VBA help frustrating, because when I lookup the Add method of
PivotCaches, it says the first parameter is the type of data and can
be
xlConsolidation, xlDatabase, xlExternal, xlPivotTable or xlScenario.
But
it
doesn't say where each option applies. I know I don't have xlEternal,
but it
might be one of the others. Is that my problem?



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

Pivot table from a pivot table
 

Your post was enough to solve my problem. I ended up using:

=1/countif($A:$A,A2)

and copied down. But I'm curious. Was the second 'A1:A15' a typo, or does it
have an application somewhere? And why use SUMPRODUCT? I don't see what
value it adds.

--
Regards,
Fred
Please reply to newsgroup, not e-mail


"Tom Ogilvy" wrote in message
...
=SUMPRODUCT(1/COUNTIF(A1:A15,A1:A15))

if there are no empty cells in the specified. range.

--
Regards,
Tom Ogilvy


Fred Smith wrote in message
...
Thanks Debra,

I thought of that, and it would solve part of problem. But I'm really
looking for the number of clients in each category. In the source data
table, a client could have 6 different accounts. I could use your
suggestion
to categorize them, but how do I count them? If I count in a pivot table
from the original data source, it's going to use 6 rather than 1. Is it
possible to count the unique entries in a data source?

--
Regards,
Fred
Please reply to newsgroup, not e-mail


"Debra Dalgleish" wrote in message
...
Instead of creating another pivot table, you could calculate the
category in the data source table.

In the data source table, add a column in which you use SUMIF, or
SUMPRODUCT to total the assets for each customer. In another column,
use
a VLOOKUP formula to calculate the category.

Add the category field to the pivot table.

Fred Smith wrote:
I'm trying to create a pivot table from an existing pivot table.

My pivot table is in the range of A1:N484. It's a list of clients
and
their
total assets. I've added column O which categorizes each client
based
on
their assets. Now I want a pivot table by category.

My code is:

Set PTCache = ActiveWorkbook.PivotCaches.Add(xlDatabase, _
Sheets("Households").Range("A2", "O483").Address)
Set PT = PTCache.CreatePivotTable(TableDestination:="", _
TableName:="AssetLevels")

But VBA gives me an error 1004 at the "Set PT" statement. PTCache is
defined
as a PivotCache, and PT is a PivotTable. If it makes any difference,
the
same variables were use to create the original pivot table.

I find VBA help frustrating, because when I lookup the Add method of
PivotCaches, it says the first parameter is the type of data and can
be
xlConsolidation, xlDatabase, xlExternal, xlPivotTable or xlScenario.
But
it
doesn't say where each option applies. I know I don't have
xlEternal,
but it
might be one of the others. Is that my problem?



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