Excel VBA宏错误:过程太大

Question

我在使用MS Excel 2016的VBA宏中遇到Procedure too Large错误。我的代码如下，但我做了一个快捷方式，这样这个想法就清晰了。

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

Answer 1

四个工作表

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