Создание макроса в Excel, который останавливается на этапе запуска кода VBA

Я создаю макрос в Excel.

Я попадаю на определенный этап автоматизации, когда мне нужно запустить некоторый код VBA.

В процессе автоматизации я нажимаю кнопку «Разработчик» и вводю то, что мне нужно для ввода.

Однако, когда я прекращаю запись и пытаюсь запустить макрос с нуля, он останавливается в точке открытия вкладки разработчика и не завершает процесс.

Sub CreatePortfolio () '

' CreatePortfolio Macro '  ' Range("A2:H132").Select ActiveWindow.SmallScroll Down:=-144 Range("D1").Select ActiveWindow.SmallScroll Down:=-6 ActiveCell.FormulaR1C1 = "Blank" Range("H1").Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = 11 .ColorIndex = 11 .TintAndShade = 0 .PatternTintAndShade = 0 End With With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With ActiveCell.FormulaR1C1 = "Name" Cells.Select ActiveWorkbook.Worksheets("INPUT_DATA").Sort.SortFields.Clear ActiveWorkbook.Worksheets("INPUT_DATA").Sort.SortFields.Add2 Key:=Range( _ "B1:B132"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal ActiveWorkbook.Worksheets("INPUT_DATA").Sort.SortFields.Add2 Key:=Range( _ "C1:C132"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("INPUT_DATA").Sort .SetRange Range("A1:H132") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Range("H14").Select ActiveWindow.SmallScroll Down:=-15 Rows("1:1").Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove ActiveWindow.SmallScroll Down:=126 Range("A133:H133").Select Selection.Copy ActiveWindow.SmallScroll Down:=-168 Range("A1").Select ActiveSheet.Paste Range("I2").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=COUNTIF(R2C8:R20000C8,RC[-8])" Range("I2").Select Selection.AutoFill Destination:=Range("I2:I133") Range("I2:I133").Select Range("J2").Select ActiveCell.FormulaR1C1 = "=IF(RC[-1]>0,1,0)" Range("J2").Select Selection.AutoFill Destination:=Range("J2:J133") Range("J2:J133").Select Range("I1").Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = 11 .ColorIndex = 11 .TintAndShade = 0 .PatternTintAndShade = 0 End With With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With ActiveCell.FormulaR1C1 = "IF1" Range("J1").Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = 11 .ColorIndex = 11 .TintAndShade = 0 .PatternTintAndShade = 0 End With With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With ActiveCell.FormulaR1C1 = "IF2" Rows("1:1").Select Selection.AutoFilter ActiveSheet.Range("$A$1:$J$133").AutoFilter Field:=10, Criteria1:="0" Range("A1:J133").Select Selection.Copy Sheets("SORT1").Select Range("A1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Columns("A:A").EntireColumn.AutoFit Range("K5").Select Columns("G:G").EntireColumn.AutoFit Columns("A:A").Select Application.CutCopyMode = False Selection.Copy Columns("L:L").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False ActiveSheet.Range("$L$1:$L$44").RemoveDuplicates Columns:=1, Header:=xlNo Columns("L:L").EntireColumn.AutoFit Range("M1").Select ActiveCell.FormulaR1C1 = "=IF(RC[-1]="""",0,1)" Range("M1").Select Selection.AutoFill Destination:=Range("M1:M4") Range("M1:M4").Select Range("M21").Select ActiveCell.FormulaR1C1 = "=SUM(R[-20]C:R[-1]C)-1" Range("L21").Select ActiveCell.FormulaR1C1 = "Magic Number" 

ЗДЕСЬ, КОГДА Я ПОПЫТАЮСЬ НАЖАТЬ РАЗРАБОТЧИК И ЗАПУСТИТЬ КОД VBA

 Range("N21").Select ActiveWindow.SmallScroll Down:=-24 Range("O2").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=RC[-3]" Range("O2").Select Selection.AutoFill Destination:=Range("O2:O20"), Type:=xlFillDefault Range("O2:O20").Select ActiveWindow.SmallScroll Down:=-15 Selection.ClearContents Range("O2").Select ActiveCell.FormulaR1C1 = "=IF(RC[-3]="""","""",RC[-3])" Range("O2").Select Selection.AutoFill Destination:=Range("O2:O20"), Type:=xlFillDefault Range("O2:O20").Select ActiveWindow.SmallScroll Down:=-27 Range("P2").Select Columns("P:P").EntireColumn.AutoFit Range("P2").Select ActiveCell.FormulaR1C1 = "=IF(RC[-1]="""","""",RC[-11])" Range("P2").Select Selection.AutoFill Destination:=Range("P2:P19"), Type:=xlFillDefault Range("P2:P19").Select ActiveWindow.SmallScroll Down:=-33 Range("O8:Y10").Select ActiveWindow.ScrollColumn = 16 ActiveWindow.ScrollColumn = 15 ActiveWindow.ScrollColumn = 12 ActiveWindow.ScrollColumn = 9 ActiveWindow.ScrollColumn = 7 ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 1 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 7 ActiveWindow.ScrollColumn = 8 ActiveWindow.ScrollColumn = 9 ActiveWindow.SmallScroll Down:=3 Range("O21").Select ActiveCell.FormulaR1C1 = "Returns " Range("O22").Select Sheets("CODE FOR VBA").Select ActiveWindow.SmallScroll Down:=-57 Range("A1:A35").Select Selection.Copy Sheets("SORT1").Select Application.CutCopyMode = False End Sub Sub Macro2() ' ' Macro2 Macro '  ' Cells.Select ActiveWorkbook.Worksheets("INPUT_DATA").Sort.SortFields.Clear ActiveWorkbook.Worksheets("INPUT_DATA").Sort.SortFields.Add2 Key:=Range( _ "B2:B133"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal ActiveWorkbook.Worksheets("INPUT_DATA").Sort.SortFields.Add2 Key:=Range( _ "C2:C133"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("INPUT_DATA").Sort .SetRange Range("A1:F133") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Range("H6").Select Columns("A:A").EntireColumn.AutoFit Range("G2").Select ActiveCell.FormulaR1C1 = "=IF(RC[-2]="""",RC[-6],"""")" Range("G2").Select Selection.AutoFill Destination:=Range("G2:G132") Range("G2:G132").Select Columns("G:G").EntireColumn.AutoFit Range("G1").Select ActiveCell.FormulaR1C1 = "Missing Data" Range("H2").Select ActiveCell.FormulaR1C1 = "=COUNTIF(R2C8:R250C8,RC[-7])" Range("H2").Select ActiveCell.FormulaR1C1 = "=COUNTIF(R2C7:R2500C7,RC[-7])" Range("H2").Select Selection.AutoFill Destination:=Range("H2:H132") Range("H2:H132").Select Range("H1").Select ActiveCell.FormulaR1C1 = "IF1" Range("I1").Select ActiveCell.FormulaR1C1 = "IF2" Range("I2").Select ActiveCell.FormulaR1C1 = "=IF(RC[-1]>0,1,0)" Range("I2").Select Selection.AutoFill Destination:=Range("I2:I132") Range("I2:I132").Select Rows("1:1").Select Range("B1").Activate Selection.AutoFilter ActiveWindow.ScrollColumn = 2 ActiveSheet.Range("$A$1:$I$132").AutoFilter Field:=9, Criteria1:="1" ActiveSheet.Range("$A$1:$I$132").AutoFilter Field:=9, Criteria1:="0" ActiveWindow.SmallScroll Down:=-15 Cells.Select Selection.Copy Sheets("SORT1").Select Cells.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Columns("G:I").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Columns("F:F").EntireColumn.AutoFit Columns("A:A").Select Selection.Copy Columns("I:I").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False ActiveSheet.Range("$I$1:$I$43").RemoveDuplicates Columns:=1, Header:=xlNo Range("J1").Select ActiveCell.FormulaR1C1 = "=IF(RC[-1]="""","""",1)" Range("J1").Select Selection.AutoFill Destination:=Range("J1:J4") Range("J1:J4").Select ActiveWindow.SmallScroll Down:=12 Range("J50").Select ActiveCell.FormulaR1C1 = "=SUM(R[-49]C:R[-1]C)-1" Range("I50").Select ActiveCell.FormulaR1C1 = "Magic Number" Range("I51").Select ActiveWindow.SmallScroll Down:=-30 Range("L1").Select ActiveWindow.SmallScroll Down:=-3 ActiveCell.FormulaR1C1 = "Market Value" Range("L2").Select ActiveCell.FormulaR1C1 = "=IF(RC[-3]="""","""",RC[-8])" Range("L2").Select Selection.AutoFill Destination:=Range("L2:L49"), Type:=xlFillDefault Range("L2:L49").Select ActiveWindow.SmallScroll Down:=-33 Range("L5").Select ActiveWindow.SmallScroll Down:=24 Range("L51").Select ActiveCell.FormulaR1C1 = "Returns" Range("M52").Select ActiveWindow.SmallScroll Down:=-27 ActiveCell.FormulaR1C1 = "" Range("M52").Select ActiveWindow.SmallScroll Down:=-30 Range("M2").Select ActiveCell.FormulaR1C1 = "=IF(RC[-1]="""","""",RC[-1]*(1+R[50]C))" Range("M2").Select Selection.AutoFill Destination:=Range("M2:M34"), Type:=xlFillDefault Range("M2:M34").Select Selection.AutoFill Destination:=Range("M2:M44"), Type:=xlFillDefault Range("M2:M44").Select Selection.AutoFill Destination:=Range("M2:X44"), Type:=xlFillDefault Range("M2:X44").Select ActiveWindow.SmallScroll Down:=0 Selection.AutoFill Destination:=Range("M2:Z44"), Type:=xlFillDefault Range("M2:Z44").Select ActiveWindow.SmallScroll Down:=-21 Selection.AutoFill Destination:=Range("M2:AA44"), Type:=xlFillDefault Range("M2:AA44").Select ActiveWindow.SmallScroll Down:=-6 Range("AA2:AA44").Select Selection.ClearContents Range("M2:Z44").Select ActiveWindow.SmallScroll Down:=9 Range("L56").Select ActiveCell.FormulaR1C1 = "Equal Weighted " Range("M56").Select ActiveCell.FormulaR1C1 = "=IF(R[-4]C="""","""",AVERAGE(R[-4]C:R[-2]C))" Range("M56").Select Selection.AutoFill Destination:=Range("M56:Z56"), Type:=xlFillDefault Range("M56:Z56").Select Range("L57").Select ActiveCell.FormulaR1C1 = "Value Weighted" Range("M57").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(R[-55]C[-1]:R[-53]C[-1],R[-5]C:R[-3]C)/SUM(R[-55]C[-1]:R[-53]C[-1])" Range("M57").Select Selection.AutoFill Destination:=Range("M57:Z57"), Type:=xlFillDefault Range("M57:Z57").Select Range("R46").Select ActiveWindow.SmallScroll Down:=9 Range("L56:Z57").Select Selection.Copy Sheets("SORT2").Select Range("A2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("A1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "Returns" Range("A2").Select End Sub  The VBA code that I run is,   Sub SplitIntoCellsPerColumn() 'updatebyExtendoffice 20160225     Dim xRg As Range     Dim xOutRg As Range     Dim xCell As Range     Dim xTxt As String     Dim xOutArr As Variant     Dim I As Long, K As Long     On Error Resume Next     xTxt = ActiveWindow.RangeSelection.Address Sel:     Set xRg = Nothing     Set xRg = Application.InputBox("please select data range:", "Kutools for Excel", xTxt,, ,, , 8)     If xRg Is Nothing Then Exit Sub     If xRg.Areas.Count > 1 Then         MsgBox "does not support multiple selections, please select again", vbInformation, "Kutools for Excel"         GoTo Sel     End If     If xRg.Columns.Count > 1 Then         MsgBox "does not support multiple columns,please select again", vbInformation, "Kutools for Excel"         GoTo Sel     End If     Set xOutRg = Application.InputBox("please select a cell to put the result:", "Kutools for Excel",, ,, ,, 8)     If xOutRg Is Nothing Then Exit Sub     I = Application.InputBox("the number of cell per column:", "Kutools for Excel",, ,, ,, 1)     If I < 1 Then         MsgBox "incorrect enter", vbInformation, "Kutools for Excel"         Exit Sub     End If     ReDim xOutArr(1 To I, 1 To Int(xRg.Rows.Count / I) + 1)     For K = 0 To xRg.Rows.Count - 1       xOutArr(1 + (K Mod I), 1 + Int(K / I)) = xRg.Cells(K + 1)     Next     xOutRg.Range("A1").Resize(I, UBound(xOutArr, 2)) = xOutArr End Sub 

Но этот код вставлен в виде модуля уже на лист, поэтому я просто открываю вкладку разработчика и нажимаю F5, чтобы ввести некоторые аргументы для запуска кода.