Generating pivot tables below each other. VBA

Question

Am trying to create a summary sheet, that has pivot data from around 13 sheets. each pivot will come below one another. Am trying for max 1 or 2 rows between each pivot.

This is the code that I am using.

    Set PSheet = Worksheets("Summary")
    Set DSheet = Worksheets(Worksheet)
    
    ' This figures out where the last pivot is placed. 
    calLastRow = PSheet.Cells.SpecialCells(xlCellTypeLastCell).row
    
    LastRow = DSheet.Cells(Rows.count, 1).End(xlUp).row
    lastCol = DSheet.Cells(1, Columns.count).End(xlToLeft).column
    Set PRange = DSheet.Cells(1, 1).Resize(LastRow, lastCol)
    
    PSheet.Activate
    
    'This sets up the pivot immediately after the old pivot. 

    Set PCache = ActiveWorkbook.PivotCaches.Create _
    (SourceType:=xlDatabase, SourceData:=PRange). _
    CreatePivotTable(TableDestination:=PSheet.Cells(calLastRow, columnNo - 1), _
    TableName:=Worksheet & "PivotTable")
    
    
    Set PTable = PCache.CreatePivotTable _
    (TableDestination:=PSheet.Cells(calLastRow, columnNo), TableName:=Worksheet & "PivotTable")

Issue that I am facing is there is 10 rows between each pivot. (Between some pivots it can be as less as 7 or 8 but never more than 11 and never less than 7)

Any idea why this is happening?? Is there any way I can reduce the gap to two rows?

PFB image below

Appears that the pivot wizard takes up 18 rows moving the xlCellTypeLastCell down by that amount even if the actual table takes less. Try calLastRow = PSheet.Usedrange.row + PSheet.usedrange.rows.count + 2 — CDP1802
– CDP1802, Commented Aug 14, 2021 at 11:29
Allwyn just another angle: Maybe you can combine sheets in a new sheet and create just one pivot table over new sheet. Of course this requires all sheets to have same kind of data. — Ozgun Senyuva
– Ozgun Senyuva, Commented Aug 14, 2021 at 13:13
It could be that the used range isn't properly calculated. Maybe you have just deleted manually some data and your actual code doesn't really catch up to that. Try to use calLastRow = PSheet.UsedRange.SpecialCells(xlCellTypeLastCell).row instead of calLastRow = PSheet.Cells.SpecialCells(xlCellTypeLastCell).row. — Evil Blue Monkey
– Evil Blue Monkey, Commented Aug 15, 2021 at 15:03
@CDP1802 Sometimes we dont see what is right in front of us.. CDP1802 can you please provide this as an answer so that i can mark it as the correct answer??? — Allwyn P
– Allwyn P, Commented Aug 16, 2021 at 8:03

CDP1802 · Accepted Answer · 2021-08-16 08:37:43Z

1

Appears that the pivot wizard takes up 18 rows moving the xlCellTypeLastCell down by that amount even if the actual table takes less. Try

calLastRow = PSheet.Usedrange.row + PSheet.usedrange.rows.count + 2

answered Aug 16, 2021 at 8:37

CDP1802

17.3k2 gold badges10 silver badges19 bronze badges

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

Collectives™ on Stack Overflow

Generating pivot tables below each other. VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related