Multiple pivot tables in one worksheet with the same data source

[sn152]

Hi All,

I am trying to create multiple pivot tables in one worksheet with the same data source. The code below is for one pivot table. I am not sure how to create the 2nd, 3rd, 4th, 5th and 6th pivot tables in the same worksheet.

Dim PSheet As Worksheet
Dim SSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim LRow As Long
Dim LCol As Long

'Insert a new worksheet for Pivot table
On Error Resume Next
Application.DisplayAlerts = False
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "PivotTable"
Application.DisplayAlerts = True
Set PSheet = Worksheets("PivotTable")
Set SSheet = Worksheets("Data")

'Define the source data range
LRow = SSheet.Cells(Rows.Count, 1).End(xlUp).Row
LCol = SSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = SSheet.Cells(1, 1).Resize(LRow, LCol)

'Create Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange).CreatePivotTable(TableDestination:=PSheet.Cells(2, 1), _
TableName:="TicketsCreated")

'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable(TableDestination:=PSheet.Cells(2, 1), TableName:="TicketsCreated")

'Insert Row Fields
With ActiveSheet.PivotTables("TicketsCreated").PivotFields("Updated by")
.Orientation = xlRowField
.Position = 1
End With

'Insert Column Fields
With ActiveSheet.PivotTables("TicketsCreated").PivotFields("Updated")
.Orientation = xlColumnField
.Position = 1
End With

'Insert Data Field
With ActiveSheet.PivotTables("TicketsCreated").PivotFields("Updated")
.Orientation = xlDataField
.Position = 1
.Function = xlCount
.NumberFormat = "#,##0"
End With

Please help me.

Thanks in advance!

 

[Chihiro]

This isn't a good idea, as it will create multiple pivot cache with same data in it, bloating workbook size and slows down performance.

It is better to set up 1 pivot table. Then copy and paste that pivot table to avoid creation of unnecessary/duplicate pivot cache.

 

[sn152]

Thanks for your reply Chihiro.

Let me explain it further more. I have to create multiple pivot tables and I need to paste all these pivot tables as a snapshot in one email. So I thought if all the pivots are in one sheet, it will be easy to put it in a mail. Pls suggest if there is any other way to do so.

Thanks!

 

[Chihiro]

You can have it in same sheet. But don't create multiple pivot from same data source.

If you are sending just the snap shot... why not manipulate pivot table filter and copy and paste to new sheet as simple data + format? Or do you need pivot control as well?

If you can attach sample workbook of your set up (your raw data, pivot table and desired out put), replacing sensitive data with innocuous data, it would help in giving you appropriate solution.

 

[Debaser]

Basically you just need to repeat this part:

Set PTable = PCache.CreatePivotTable(TableDestination:=PSheet.Cells(2, 1))

with a different destination (it's not clear where you want the others) and adjust the code below to use the PTable variable rather than referring to the table by name. Will they all have the same layout?

 

[sn152]

Thanks for the reply Debaser & Chihiro.

I have created multiple pivots in multiple sheets as it makes the work easier. Thanks!