VBA 代码适用于宏，但不适用于按钮

Question

当我在宏中逐行(F8)时，以下代码运行良好。但是，当我将其复制到工作表“Cover”中的按钮时，转到“Worksheets("FES LIST").Range(Cells(2, 2), Cells(LastRow2, 4)).Select”时出现错误。然后我在工作表“FES LIST”中创建了另一个按钮，这是我执行“选择”的工作表，代码再次开始工作。

有没有一种方法可以让我将按钮保留在工作表“封面”中，但仍然在工作表“FES LIST”中选择范围？非常感谢。

Private Sub CommandButton1_Click()

'This Code will remove any names defined in name manager
    Dim nm As Name

    On Error Resume Next
    For Each nm In ActiveWorkbook.Names
        nm.Delete
    Next
    On Error GoTo 0

'Find the last used row in a Column B and select range from B2 to D:LastRow
  Dim LastRow2 As Long

    With Worksheets("FES LIST")
        LastRow2 = .Cells(.Rows.Count, "B").End(xlUp).Row

    Sheets("FES LIST").Activate

    Worksheets("FES LIST").Range(Cells(2, 2), Cells(LastRow2, 4)).Select
    Worksheets("FES LIST").Range(Cells(2, 2), Cells(LastRow2, 4)).Name = "NameRange0"

    End With
End Sub

Answer 1

改变

Sheets("FES LIST").Activate

Worksheets("FES LIST").Range(Cells(2, 2), Cells(LastRow2, 4)).Select
Worksheets("FES LIST").Range(Cells(2, 2), Cells(LastRow2, 4)).Name = "NameRange0"

至

With Worksheets("FES LIST")
    .Range(.Cells(2, 2), .Cells(LastRow2, 4)).Name = "NameRange0"
End With

它不使用 .Activate/.Select 并且完全限定 Cells 对象。

经过尝试和测试

Private Sub CommandButton1_Click()
    Dim nm As Name
    Dim LastRow2 As Long

    On Error Resume Next
    For Each nm In ActiveWorkbook.Names
        nm.Delete
    Next
    On Error GoTo 0

    With Worksheets("FES LIST")
        LastRow2 = .Cells(.Rows.Count, "B").End(xlUp).Row

        .Range(.Cells(2, 2), .Cells(LastRow2, 4)).Name = "NameRange0"
    End With
End Sub