Export as PDF Loop maxes out at 21 iterations

[potterfan]

Trying to implement this code to a loop in Excel for Mac 365:

Sub SaveReportAsPDFIn2020()

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    'Ben Matson : 5-June-2020
    'Test macro to save as pdf with ExportAsFixedFormat

    Dim filename As String
    Dim Folderstring As String
    Dim FilePathName As String
    Dim Report As Variant
    Dim ws As Sheets
    Dim sh As Worksheet
    
    Set ws = Sheets
    Set Report = ThisWorkbook.Sheets(Array("Cover Sheet", "Ag Loss Sensitivity", _
                        "Experience Rating Sheet", "Loss Ratio Analysis", _
                        "Mod Analysis&Strategy Proposal", "Mod Snapshot", _
                        "Mod & Potential Savings"))
    
    
        ws("Cover Sheet").PageSetup.PrintArea = Range("A1:G37").Address
        ws("Ag Loss Sensitivity").PageSetup.PrintArea = Range("A1:H55").Address
        ws("Experience Rating Sheet").PageSetup.PrintArea = Range("A4:L322,A324:M340").Address
        ws("Loss Ratio Analysis").PageSetup.PrintArea = Range("A1:M54").Address
        ws("Mod Analysis&Strategy Proposal").PageSetup.PrintArea = Range("A1:M44").Address
        ws("Mod Snapshot").PageSetup.PrintArea = Range("A1:O69").Address
        ws("Mod & Potential Savings").PageSetup.PrintArea = Range("A1:L80").Address

    
    
    'Name of the pdf file
    filename = ThisWorkbook.Sheets("Cover Sheet").Range("B20") & "_Emod" & "_" & ThisWorkbook.Sheets("Yearly Breakdown").Range("F2") & ".pdf"
    
    'Path Creation and Setting
    Folderstring = "/Users/ben/Desktop/Emod_Calc/Emods_2"
    FilePathName = Folderstring & Application.PathSeparator & filename
    
    'Selecting what sheets to Print
    Report.Select
   
    'Prints as PD
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
    FilePathName, Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False
    
    'Clears Print Area
    For Each sh In Report
        sh.PageSetup.PrintArea = ""
    Next sh

    'Clears the variables
    Set Report = Nothing
    filename = ""
    Folderstring = ""
    FilePathName = ""
    
    ThisWorkbook.Sheets("Yearly Breakdown").Select
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    
End Sub

Whenever I run associated loop, on the 21st iteration excel crashes.

Only other thing I see happening is the Memory usage capping at 2GB right before crash.

Any help is appreciated.

 

[Dermot]

The most memory intensive part of the report is surely the Report variable containing an array of all the sheets

There's no need for this array - you could instead substitute an array of just the sheet names and loop through that - I would try that

 

[potterfan]

The most memory intensive part of the report is surely the Report variable containing an array of all the sheets

There's no need for this array - you could instead substitute an array of just the sheet names and loop through that - I would try that

ooooh.... could you give e an example of what you mean?

 

[potterfan]

The most memory intensive part of the report is surely the Report variable containing an array of all the sheets

There's no need for this array - you could instead substitute an array of just the sheet names and loop through that - I would try that

Oh you mean loop through to select them then print them?
Can you select multiple sheets in a loop and they all are the same active sheet?

 

[Dermot]

Actually, having looked at the print options, you can specify the sheet number to start from and end on (like Word).
If you can arrange the sheets in the correct order, it's worth trying.

 

[potterfan]

Actually, having looked at the print options, you can specify the sheet number to start from and end on (like Word).
If you can arrange the sheets in the correct order, it's worth trying.

View attachment 19109

Tried it out. still does the same thing. also found out that it is not necessarily the memory that is maxing out. its just after 21 iterations it fails. Im thinking its like a hidden memory leak.