CODEWITHSUNDEEP
excelvba
Allwyn P
Allwyn P

Reputation: 43

Generating pivot tables below each other. VBA

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

enter image description here

Upvotes: 0

Views: 367

Answers (1)

CDP1802
CDP1802

Reputation: 16357

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

Upvotes: 1

Related Questions