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

2

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

answered Oct 18, 2017 at 9:02

Subodh Tiwari sktneer

9,9862 gold badges20 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shai Rado Over a year ago

@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"

Subodh Tiwari sktneer Over a year ago

@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

0

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.

answered Oct 18, 2017 at 10:37

ashleedawg

21.9k9 gold badges82 silver badges120 bronze badges

Collectives™ on Stack Overflow

Two Pivot Tables in the same excel sheet

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related