VBA code breaks if lines are in excess of 40,000 approx

[tezza]

Hi, again...

I have a code that runs very well if there are say, 15,000 lines in the worksheet but fails at higher number of rows

The code stops and highlights the code at this part of the code:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    SrcData, Version:=6).CreatePivotTable TableDestination:= _
    "Hours!R1C1", TableName:="Staff_Hours", DefaultVersion:=6

A pop up box shows at in the image

The code in that section of the VBA is:

Sheets("Sheet1").Select
'ScrData =
SrcData = ActiveSheet.Name & "!" & Range("A1", Range("A1").SpecialCells(xlLastCell)).Address(ReferenceStyle:=xlR1C1)
Sheets.Add(After:=ActiveSheet).Name = "Hours"
Sheets("Hours").Select

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    SrcData, Version:=6).CreatePivotTable TableDestination:= _
    "Hours!R1C1", TableName:="Staff_Hours", DefaultVersion:=6
Set pvt = ActiveSheet.PivotTables("Staff_Hours")
'Add item to filter
pvt.PivotFields("Staff Staff Pay Group").Orientation = xlPageField
pvt.PivotFields("Chargeable Rate Sheet").Orientation = xlPageField
'Add item to the Row Labels
    pvt.PivotFields("Staff First Name Staff Last Name").Orientation = xlRowField
'Add items to Values
pfName = "Hours"
    pvt.AddDataField pvt.PivotFields("Rounded"), pfName, xlSum
pfName = "BasicP"
    pvt.AddDataField pvt.PivotFields("Basic"), pfName, xlSum
pfName = "DomP"
    pvt.AddDataField pvt.PivotFields("Dom_care"), pfName, xlSum
pfName = "Spt"
    pvt.AddDataField pvt.PivotFields("Specialist Task"), pfName, xlSum
pfName = "WeekendP"
    pvt.AddDataField pvt.PivotFields("Weekend"), pfName, xlSum
pfName = "OfficeP"
    pvt.AddDataField pvt.PivotFields("Office"), pfName, xlSum
pfName = "On-Call"
    pvt.AddDataField pvt.PivotFields("DC/On-Call"), pfName, xlSum
pfName = "TT"
    pvt.AddDataField pvt.PivotFields("Travel Time"), pfName, xlSum
pvt.CalculatedFields.Add Name:="All Staff Mileage", Formula:="=Staff Mileage + Community Mileage"
pfName = "All_staff_mileage"
    pvt.AddDataField pvt.PivotFields("All Staff Mileage"), pfName, xlSum
 
    
    
Sheets.Add(After:=ActiveSheet).Name = "Mileage"
Sheets("Mileage").Select
      
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    SrcData, Version:=6).CreatePivotTable TableDestination:= _
    "Mileage!R1C1", TableName:="Client_Mileage", DefaultVersion:=6
Set pvt = ActiveSheet.PivotTables("Client_Mileage")
pvt.PivotFields("Chargeable Rate Sheet").Orientation = xlPageField
'Add item to the Row Labels
    pvt.PivotFields("Client First Name Client Last Name").Orientation = xlRowField
'Add items to Values
    pfName = "Miles"
    pvt.AddDataField pvt.PivotFields("Mileage"), pfName, xlSum
    pfName = "Comm_Miles"
    pvt.AddDataField pvt.PivotFields("Community Mileage"), pfName, xlSum

End Sub

 

[Alex Blakenburg]

Do you have these lines in your code ? If not try adding them to the beggining and end of your code and see if it makes a difference.

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    
    
    ' The rest of your code here
    
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True

 

[tezza]

Do you have these lines in your code ? If not try adding them to the beggining and end of your code and see if it makes a difference.

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
   
   
    ' The rest of your code here
   
   
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True

Ahhh perfect, that runs in about 5 seconds

Thank you

I'm not familiar with these two so I'll look them up

 Application.Calculation = xlCalculationManual
    Application.EnableEvents = False

 

[Alex Blakenburg]

If you don't have any worksheet or workbook event macros you don't need Application.EnableEvents, it just turns triggering them off then back on.
When you are doing bulk updates in a macro you generally don't want events triggered.

Turning calculations Off then back on in theory shouldn't make much difference when you are only creating pivot tables.
If you are progressively adding, sorting, deleting data in a spreadsheet you don't need Excel to recalculate at every step, you only need a final recalculation done when you finish.