VBA code works in Macro but not on a button

Question

The following code works well when I go line by line (F8) in Macro. However when I copy it onto the button in sheet "Cover", it has a error when goes to " Worksheets("FES LIST").Range(Cells(2, 2), Cells(LastRow2, 4)).Select". Then I created another button in worksheet "FES LIST", which is the sheet where I perform 'Select', the code starts working again.

Is there a way in which I can leave the button in sheet "cover" but still select a range in sheet "FES LIST"? Many thanks.

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

Siddharth Rout Siddharth Rout · Accepted Answer · 2017-06-03T09:50:46

Change

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"

To

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

Which doesn't use .Activate/.Select and also fully qualifies the Cells object.

Tried and Tested

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

VBA code works in Macro but not on a button

1 Answers