Hello! I've been running into an issue with a loop macro that after about 18 iterations it just shuts down. I have it Calculating a couple of times so that the values update correctly. One of things being updated is a dynamic named range that was made with
indirect(). What can I do to clean up the code and keep it from crashing?
Sub CalculateEmods() Application.ScreenUpdating = False Dim filename As String Dim FolderName As String Dim Folderstring As String Dim FilePathName As String Dim ws As Worksheet Dim Calculator As Variant Dim xprating As Variant Dim emod As Range Dim member As Range Dim emodsws As Variant Dim memberfound As Variant Dim i As Integer Dim RowCount As Integer Dim NeededEmods As Range Dim Report As Variant Set Calculator = ThisWorkbook.Sheets(Array("Loss Template", "Codes", "Rating Data", "Yearly Breakdown", "Cover Sheet", "Ag Loss Sensitivity", "Experience Rating Sheet", "Loss Ratio Analysis", "Mod Analysis&Strategy Proposal", "Mod Snapshot", "Mod & Potential Savings")) Set xprating = ThisWorkbook.Sheets("Experience Rating Sheet") Set emod = ThisWorkbook.Sheets("Yearly Breakdown").Range("G334") Set member = ThisWorkbook.Sheets("Yearly Breakdown").Range("B2") Set emodsws = ThisWorkbook.Sheets("2020Emods") Set NeededEmods = emodsws.Range("A2", Range("A2").End(xlDown)) Set memberfound = NeededEmods.Find(member) FolderName = "EmodFolder" RowCount = NeededEmods.Rows.Count + 1 Report = Array("Cover Sheet", "Ag Loss Sensitivity", "Experience Rating Sheet", "Loss Ratio Analysis", "Mod Analysis&Strategy Proposal", "Mod Snapshot", "Mod & Potential Savings") For i = 2 To RowCount Application.EnableEvents = False member.Value2 = emodsws.Range("A" & i).Value2 'Updates Report for newly entered member For Each ws In Calculator ws.Calculate Next ws For Each ws In Calculator ws.Calculate ws.PageSetup.RightFooter = Sheet17.Range("B3").Text & Chr(10) & "Mod Effective Date: " & Sheet17.Range("B4") Next ws Application.EnableEvents = True xprating.Calculate 'Copies emod and pastes it to Emod Worksheet emodsws.Cells(i, 4).Value2 = emod.Value2 'Prints Emod Report for member as PDF filename = ActiveWorkbook.Sheets("Cover Sheet").Range("B20") & "_Emod" & ".pdf" Folderstring = CreateFolderinMacOffice2016(NameFolder:=FolderName) FilePathName = Folderstring & Application.PathSeparator & filename ThisWorkbook.Sheets(Report).Select ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _ FilePathName, Quality:=xlQualityStandard, _ IncludeDocProperties:=True, IgnorePrintAreas:=False emodsws.Select 'Saves Workbook after Calculation ThisWorkbook.Save Next i Application.ScreenUpdating = True MsgBox "Emod Report Updated!" End Sub