I have a large workbook; on Sheet1 is an inventory containing 10K lines of
data, in Cols A-L. My code ('SizeCP') first inserts formulas (in Cols M-AU)
for each of these lines. It then calls 'SizeCS', which goes to Sheet2 and
inserts formulas in Col A-AU, for as many rows as there are in Sheet1.
Lastly, it calls 'SizeSchedule', which goes to Sheet3 and inserts formulas in
Cols A-S, for as many rows as there are in Sheet1. The system bombs out in
the middle part of the 'SizeSchedule' macro with "Excel cannot complete this
task with available resources.", and I can't figure out why. I've changed
calculation to manual, tried putting all the Sheet3 data on Sheet1 (so the
formulas wouldn't have to refer to a different sheet), dropped all the other
code from the workbook to see if that made a difference, tested it on another
PC (and in another workbook), and stepped through using watch to look at the
variables. It doesn't always crash at the same place in the 'SizeInventory'
macro, but it usually happens around Columns D/E; every once in a while it
will run thru okay, but 99.9% of the time it doesn't. I have plenty of
memory in my PC, and no other apps running. Below is my code; I've removed
some of the formulas that are in it, just to shorten it for easier reading.
Could someone look at it and see if they can figure what may be wrong here;
I'm just at my wits end, having worked on this for 5 days now.

Option Explicit
Sub SizeInventoryBox()
Call SizeCP
Call SizeCS
Call SizeSchedule
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Sub SizeCP()
Dim LastRow As Range
Dim FirstRow As Range
Dim wks As Worksheet
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False
Worksheets("Sheet1").Unprotect
Worksheets("Sheet1").Select
With wks
Set wks = ActiveSheet
Set FirstRow = Range("M65536").End(xlUp).Offset(1, 0)
Set LastRow = Range("G65536").End(xlUp).Offset(0, 6)
End With
Range(FirstRow, LastRow).Formula = "=IF(ISERROR(IF(RC[-1]0,(VLOOKUP(RC
[-1],SC,2,FALSE)),"""")),"""",IF(RC[-1]0,(VLOOKUP(RC[-1],SC,2,FALSE)),""""))"
Range(FirstRow.Offset(0, 16), LastRow.Offset(0, 16)).Formula =
"=IF(RC[-12]="""","""",IF(RC[-1]<0,RC[-1],CPDISCOUNT))"
'NOTE--THE ABOVE 'FORMULA SYNTAX' IS REPEATED, WITH DIFFERENT FORMULAS AND
OFFSET REFERENCES, FOR COLUMNS M-AU
Set FirstRow = Nothing
Set LastRow = Nothing
Set wks = Nothing
ActiveSheet.Protect
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Sub SizeCS()
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False
Worksheets("Sheet2").Unprotect
Worksheets("Sheet2").Select
Range(Range("AS65536").End(xlUp).Offset(1, 0),
Range("G65536").End(xlUp).Offset(0, 38)).Formula =
"=IF(RC[-30]=5,""Sheet1"","""")"
'NOTE--THE ABOVE 'FORMULA SYNTAX' IS REPEATED, WITH DIFFERENT FORMULAS AND
OFFSET REFERENCES, FOR COLUMNS A-AU
ActiveSheet.Protect
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Sub SizeSchedule()
Dim LasRowCP As Long
Dim LastRowCPP As Long
LastRowCP = Worksheets("Sheet1").Cells(Rows.Count, 3).End(xlUp).Row
LastRowCPP = Worksheets("Sheet1").usedrange.Rows.Count + 10
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False
Worksheets("Sheet3").Unprotect
Worksheets("Sheet3").Select
Range("A13" & ":" & "A" & LastRowCPP).Formula =
"=IF(OR('Sheet1'!R[-10]C31=0,'Sheet1'!R[-10]C31=""""),"""",'Sheet1'!R[-10]C7)"
Range("B13" & ":" & "B" & LastRowCPP).Formula =
"=IF(OR('Sheet1'!R[-10]C31=0,'Sheet1'!R[-10]C31=""""),"""",'Sheet1'!R[-10]C14)"
'NOTE--THE ABOVE 'FORMULA SYNTAX' IS REPEATED, WITH DIFFERENT FORMULAS, FOR
COLUMNS A-S; IT USUALLY CRASHES AROUND COLS D OR E, BUT SOMETIMES GETS ALL
THE WAY THRU R BEFORE CRASHING
'ERASE ANYTHING LEFT OVER FROM A PREVIOUS SIZING
Range(Cells(LastRowCP + 11, 1),
Range("A65536").End(xlDown)).EntireRow.ClearConten ts
LastRowCP = 0
LastRowCPP = 0
ActiveSheet.Protect
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub