Two Pivot Tables in the same excel sheet

Question

I have created a Pivot Table in the same sheet with the source data. Now, I want to create a second Pivot Table in the same sheet with same data source, but with different printed data inside the Pivot Table (of course). I keep getting an error, as it says that Pivot Table cannot overwrite another Pivot Table. However, they way I have selected the ranges where the pivot tables are created, they dont overwrite each other. I tested manually too. Also, if I record the process it just uses exact references for the source data, which I dont want to use because the lastrow and lastcolumn change daily. Just to remind it, the macro works great for creating the first table. The second table is the issue. Below, I will provide my code as it is now.

Dim DSheet As Worksheet Dim PCache As PivotCache Dim PTable1 As PivotTable Dim PTable2 As PivotTable Dim PRange As Range Application.DisplayAlerts = True Set DSheet = Worksheets("Budget_Report") Set PRange = DSheet.Range(Cells(1, 53), Cells.SpecialCells(xlCellTypeLastCell)) Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange) Set PTable1 = PCache.CreatePivotTable(TableDestination:=DSheet.Cells(4, 1), TableName:="PivotTable1") Set PTable2 = PCache.CreatePivotTable(TableDestination:=DSheet.Cells(17, 2), TableName:="PivotTable2") With ActiveSheet.PivotTables("PivotTable1") With .PivotFields("T-Lane") .Orientation = xlRowField .Position = 1 .Subtotals(1) = True .Subtotals(1) = False End With With .PivotFields("Monthly Cost FCST") .Orientation = xlDataField .Position = 1 .Function = xlAverage .Caption = "Monthly Cost Forecast" End With End With With ActiveSheet.PivotTables("PivotTable2") With .PivotFields("Oran") .Orientation = xlRowField .Position = 1 .Subtotals(1) = True .Subtotals(1) = False End With With .PivotFields("Monthly Cost FCST") .Orientation = xlDataField .Position = 1 .Function = xlAverage .Caption = "Monthly Cost Forecast" End With End With

Without seeing your data it is hard to tell if or how the pivot tables will overlap. Why do you use code to create the pivot tables? The idea with pivot tables is that only the data changes, so the pivot table definitions stay the same and you only need to refresh them. Also, consider using Excel Table Objects as the source for pivot tables, then the data source will grow and shrink dynamically. Books have been written about this subject, so you may want to do a bit more research and learning about pivot tables and dynamic data sources. — teylyn
– teylyn, Commented Oct 18, 2017 at 8:55
@teylyn thanks for the feedback! I wanted to create them automatically, because this macro will run inside different workbooks daily. (I will be copy pasting it daily to other sheets! — Pericles Faliagas
– Pericles Faliagas, Commented Oct 18, 2017 at 8:57

Subodh Tiwari sktneer · Accepted Answer · 2017-10-18 09:02:13Z

When you insert the first pivot table and insert another one before populating the first one, the empty pivot table range of the first pivot table occupies the destination cell [DSheet.Cells(17, 2)] of the second pivot table.

I think you should insert the first pivot table and populate it and then insert another one and populate that as well.

See if that resolves your issue.

Dim DSheet As Worksheet Dim PCache As PivotCache Dim PTable1 As PivotTable Dim PTable2 As PivotTable Dim PRange As Range Application.DisplayAlerts = True Set DSheet = Worksheets("Budget_Report") Set PRange = DSheet.Range(Cells(1, 53), Cells.SpecialCells(xlCellTypeLastCell)) Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange) Set PTable1 = PCache.CreatePivotTable(TableDestination:=DSheet.Cells(4, 1), TableName:="PivotTable1") With ActiveSheet.PivotTables("PivotTable1") With .PivotFields("T-Lane") .Orientation = xlRowField .Position = 1 .Subtotals(1) = True .Subtotals(1) = False End With With .PivotFields("Monthly Cost FCST") .Orientation = xlDataField .Position = 1 .Function = xlAverage .Caption = "Monthly Cost Forecast" End With End With Set PTable2 = PCache.CreatePivotTable(TableDestination:=DSheet.Cells(17, 2), TableName:="PivotTable2") With ActiveSheet.PivotTables("PivotTable2") With .PivotFields("Oran") .Orientation = xlRowField .Position = 1 .Subtotals(1) = True .Subtotals(1) = False End With With .PivotFields("Monthly Cost FCST") .Orientation = xlDataField .Position = 1 .Function = xlAverage .Caption = "Monthly Cost Forecast" End With End With

@sktneer just curious why you are not using your Pivot objects after you Set them ? Instead of With ActiveSheet.PivotTables("PivotTable1") you can use With PTable1, the same for "PivotTable2"
@ShaiRado I didn't change the OP's code just removed the line and placed it at the correct position in order to avoid the error OP was getting. I was not supposed to rewrite the code though. :)

ashleedawg · Accepted Answer · 2017-10-18 10:37:02Z

From the MSDN doc on pivot tables:

"Important" If you define multiple PivotTables on the Excel Worksheet, it is possible the resulting tables can grow and overlap each other if the Activity returns a large dataset. In this scenario, you will receive "A PivotTable report cannot overlap another PivotTable report" when you refresh the data. You can correct this error by adding addition columns or rows between the pivot tables to allow the tables to grow with out overlapping.

Collectives™ on Stack Overflow

Two Pivot Tables in the same excel sheet

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related