Pivot Table / combining recordsets

  • Thread starter Thread starter MFRASER
  • Start date Start date
You could create a Pivot Table from multiple consolidation ranges:

1. Choose Data>PivotTable and PivotChart Report
2. Select Multiple consolidation ranges, click Next
3. Select one of the page options, click Next
4. Select each range, and click Add, click Next
5. Select a location for the PivotTable, click Finish

However, you won't get the same pivot table layout that you'd get from a
single range.

For example, if customer is the first column in your data source, the
row heading should show the customer names. If remaining columns are
Units Sold, Product#, Unit Price and Total, the column area will show
each of those headings. You can change the function that's being used by
the data value, but it will use the same function on all these columns.

The Pivot Table would contain some meaningless data, such as sum of
Product# or columns full of zeros if the database columns contain text.
To avoid this, you can rearrange your database columns, and then use
data ranges that only include the columns that you want to total.

If possible, move your data to a single worksheet, or store it in a
database, such as MS Access, and you'll have much more flexibility in
creating the pivot table.
 
How do I do this inside of VBA code?


Debra Dalgleish said:
You could create a Pivot Table from multiple consolidation ranges:

1. Choose Data>PivotTable and PivotChart Report
2. Select Multiple consolidation ranges, click Next
3. Select one of the page options, click Next
4. Select each range, and click Add, click Next
5. Select a location for the PivotTable, click Finish

However, you won't get the same pivot table layout that you'd get from a
single range.

For example, if customer is the first column in your data source, the
row heading should show the customer names. If remaining columns are
Units Sold, Product#, Unit Price and Total, the column area will show
each of those headings. You can change the function that's being used by
the data value, but it will use the same function on all these columns.

The Pivot Table would contain some meaningless data, such as sum of
Product# or columns full of zeros if the database columns contain text.
To avoid this, you can rearrange your database columns, and then use
data ranges that only include the columns that you want to total.

If possible, move your data to a single worksheet, or store it in a
database, such as MS Access, and you'll have much more flexibility in
creating the pivot table.
Click to expand...
 
Thanks for the example. I am not able to view my data because I am doing
the following

Dim pCache As Excel.PivotCache
Dim pTable As PivotTable

Set pCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
Set pCache.Recordset = rst
Set pTable = pCache.CreatePivotTable(sheet.Range("A8"))

Set AddPivotTable = pTable

Is this not the correct way to setup the pivottable?
 
I was able to create two Pivot tables on the same sheet I would like to
create a third that containst the data fo the other two pivot tables, is
this possible?
 
Back
Top