Excel VBA Macro Error: Procedure too Large

Question

I'm getting Procedure too Large error in a VBA macro using MS Excel 2016. My code is as follows but I made a shortcut so that the idea clears up.

Sub Entry_Click()
    If Sheet1.Range("M4").Value = Sheet3.Range("M17").Value Then
        Application.ScreenUpdating = False
        Dim iRow As Long
        iRow = Sheets(2).Range("A1048576").End(xlUp).Row + 1
        If iRow <> 16 Then iRow = iRow + 9
        With ThisWorkbook.Sheets(2)
            .Range("A" & iRow).Value = Sheet1.Range("C5").Value
            .Range("B" & iRow).Value = Sheet1.Range("D5").Value
        End With
        Application.ScreenUpdating = True
    ElseIf Sheet1.Range("M4").Value = Sheet3.Range("M19").Value Then
        Application.ScreenUpdating = False
        iRow = Sheets(4).Range("A1048576").End(xlUp).Row + 1
        If iRow <> 16 Then iRow = iRow + 9
        With ThisWorkbook.Sheets(4)   
            .Range("A" & iRow).Value = Sheet1.Range("C5").Value 
            .Range("B" & iRow).Value = Sheet1.Range("D5").Value
        End With
    End If
End Sub

Since you are using worksheet code names for `Sheet1` and `Sheet3`, why are you not using them for `Sheet2` and `Sheet4`? Are they (2 & 4) in a different workbook or did you change their names? Could you describe what the code is supposed to do, maybe backup with a screenshot or two? — VBasic2008, Mar 14 '21 at 13:09
You've attempted to reduce the code to a simpler version, which is very good; but can you confirm that the same error happens with this code? From the answers on [this question](https://stackoverflow.com/q/3751263/111794) it seems this error pops up with very large procedures. Also, at which line does the error happen? — Zev Spitz, Mar 14 '21 at 15:04

score 0 · Answer 1 · answered Mar 14 '21 at 13:37

Four Worksheets

Option Explicit

Sub Entry_Click()

    Application.ScreenUpdating = True

    Dim iRow As Long
    If Sheet1.Range("M4").Value = Sheet3.Range("M17").Value Then
        With Sheet2 'ThisWorkbook.Worksheets(2)
            iRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
            If iRow <> 16 Then
                iRow = iRow + 9
            End If
            .Range("A" & iRow).Value = Sheet1.Range("C5").Value
            .Range("B" & iRow).Value = Sheet1.Range("D5").Value
        End With
     ElseIf Sheet1.Range("M4").Value = Sheet3.Range("M19").Value Then
        With Sheet4 ' ThisWorkbook.Worksheets(4)
            iRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
            If iRow <> 16 Then
                iRow = iRow + 9
            End If
            .Range("A" & iRow).Value = Sheet1.Range("C5").Value
            .Range("B" & iRow).Value = Sheet1.Range("D5").Value
        End With
    End If
    
     Application.ScreenUpdating = False

End Sub

Excel VBA Macro Error: Procedure too Large

1 Answers1

Four Worksheets