Excel Pivot Table Limitations

[corporatechain]

I am trying to create a pivot table for around 600,000 rows after merging data over several tabs into one worksheet using this vba code:

Sub PivotTableLoop()


Dim WS_Count As Integer
Dim I As Integer
Dim LastRow As Long


' Copy first set of data from tab Month_12 - Start of Financial Year 2017


    Sheets.Add After:=ActiveSheet
    ActiveSheet.Name = "DataMerge"
    Sheets("Month_12").Select
    Range("A1", Selection.End(xlDown).Offset(-1, 0)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("DataMerge").Select
    ActiveSheet.Paste
    
WS_Count = ActiveWorkbook.Worksheets.Count


For I = 13 To WS_Count
    
Sheets(I).Select
Range("A2", Selection.End(xlDown).Offset(-1, 0)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("DataMerge").Select
LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
ActiveSheet.Cells(LastRow + 1, "A").Select
ActiveSheet.Paste


Next I


End Sub

Is there any way to overcome this barrier without excel crashing as I add more pivot fields

 

[RoryA]

What barrier are you trying to overcome?

 

[corporatechain]

The pivot table eventually just crashes as I add more pivot fields as Excel runs out of memory I presume.

 

[RoryA]

Your code doesn't relate to a pivot table so at what point is Excel actually crashing?

 

[corporatechain]

 Sub PivotTable()
'
' PivotTable Macro
'
     
    Dim PTCache As PivotCache
    Dim PT As PivotTable
    Dim rSourceData As Range
    Dim LastRow As Long
    
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    
    Set rSourceData = Range("A1:V" & LastRow)
    
    Worksheets.Add().Name = "Pivot"
    
    Set PTCache = ActiveWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=rSourceData)
    
    Set PT = ActiveSheet.PivotTables.Add( _
        PivotCache:=PTCache, _
        TableDestination:=Range("A3"), TableName:="PivotData")
        
    ActiveSheet.PivotTables("PivotData").AddDataField ActiveSheet.PivotTables( _
        "PivotData").PivotFields("Total Hours"), "Sum of Total Hours", xlSum
    With ActiveSheet.PivotTables("PivotData").PivotFields("Profit Centre Name")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotData").PivotFields("WBS (Code)")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("PivotData").PivotFields("WBS (Desc)")
        .Orientation = xlRowField
        .Position = 3
    End With
    With ActiveSheet.PivotTables("PivotData").PivotFields("Task")
        .Orientation = xlRowField
        .Position = 4
    End With
    With ActiveSheet.PivotTables("PivotData").PivotFields("Project Key")
        .Orientation = xlRowField
        .Position = 5
    End With
    With ActiveSheet.PivotTables("PivotData").PivotFields("Billing Key")
        .Orientation = xlRowField
        .Position = 6
    End With
    With ActiveSheet.PivotTables("PivotData").PivotFields("Employee Name")
        .Orientation = xlRowField
        .Position = 7
    End With
    With ActiveSheet.PivotTables("PivotData").PivotFields("Employee ID")
        .Orientation = xlRowField
        .Position = 8
    End With
    With ActiveSheet.PivotTables("PivotData").PivotFields("Fiscal Week (WE Date)" _
        )
        .Orientation = xlColumnField
        .Position = 1
    End With

    ActiveSheet.PivotTables("PivotData").RowAxisLayout xlTabularRow
    
    'Turning OFF Sub-Totals for all pivot fields
    ActiveSheet.PivotTables("PivotData").PivotFields("Fiscal Period").Subtotals _
        = Array(False, False, False, False, False, False, False, False, False, False, False, False _
        )
    ActiveSheet.PivotTables("PivotData").PivotFields("Fiscal Week").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotData").PivotFields("Fiscal Week (WE Date)"). _
        Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
        False, False)
    ActiveSheet.PivotTables("PivotData").PivotFields("Cost Centre").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotData").PivotFields("Cost Centre Name"). _
        Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
        False, False)
    ActiveSheet.PivotTables("PivotData").PivotFields("Charge to ProfitCtr"). _
        Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
        False, False)
    ActiveSheet.PivotTables("PivotData").PivotFields("Profit Centre Name"). _
        Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
        False, False)
    ActiveSheet.PivotTables("PivotData").PivotFields("WBS (Code)").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotData").PivotFields("WBS (Desc)").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotData").PivotFields("Employee Company Desc"). _
        Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
        False, False)
    ActiveSheet.PivotTables("PivotData").PivotFields("Employee ID").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotData").PivotFields("Employee Name").Subtotals _
        = Array(False, False, False, False, False, False, False, False, False, False, False, False _
        )
    ActiveSheet.PivotTables("PivotData").PivotFields("Employee Type").Subtotals _
        = Array(False, False, False, False, False, False, False, False, False, False, False, False _
        )
    ActiveSheet.PivotTables("PivotData").PivotFields("Employee Subgroup"). _
        Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
        False, False)
    ActiveSheet.PivotTables("PivotData").PivotFields("Attendance Code"). _
        Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
        False, False)
    ActiveSheet.PivotTables("PivotData").PivotFields("Attendance Description"). _
        Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
        False, False)
    ActiveSheet.PivotTables("PivotData").PivotFields("Project Key").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotData").PivotFields("Billing Key").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotData").PivotFields("Task").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotData").PivotFields("Comment").Subtotals = Array _
        (False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotData").PivotFields("Reference Code").Subtotals _
        = Array(False, False, False, False, False, False, False, False, False, False, False, False _
        )
   'Turning on SubTotals for WBS Codes and Profit Centre Names
    ActiveSheet.PivotTables("PivotData").PivotFields("Total Hours").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    
    ActiveSheet.PivotTables("PivotData").PivotFields("Profit Centre Name"). _
        Subtotals = Array(True, False, False, False, False, False, False, False, False, False, _
        False, False)
    
    ActiveSheet.PivotTables("PivotData").PivotFields("WBS (Code)").Subtotals = _
        Array(True, False, False, False, False, False, False, False, False, False, False, False)


End Sub

This is my code for the pivot table but as it gets down to the bolded section, its says Excel has run out of memory (just for reference - the last row is around 400,000ish cells)

 

[RoryA]

How many rows are in the pivot at that point and how many unique items do you have in each field? There are limitations for that in pivot tables that you might be hitting.