Pivot table from a pivot table

  • Thread starter Thread starter Fred Smith
  • Start date Start date
F

Fred Smith

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?
 
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.
 
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?
 
=SUMPRODUCT(1/COUNTIF(A1:A15,A1:A15))

if there are no empty cells in the specified. range.
 
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.
 
Back
Top