Select method of Range class failed via VBA

Solution 1:

I believe you are having the same problem here.
The sheet must be active before you can select a range on it.

Also, don't omit the sheet name qualifier:

Sheets("BxWsn Simulation").Select
Sheets("BxWsn Simulation").Range("Result").Select

Or,

With Sheets("BxWsn Simulation")
  .Select
  .Range("Result").Select
End WIth

which is the same.

Solution 2:

The correct answer to this particular questions is "don't select". Sometimes you have to select or activate, but 99% of the time you don't. If your code looks like

Select something
Do something to the selection
Select something else
Do something to the selection

You probably need to refactor and consider not selecting.

The error, Method 'Range' of object '_Worksheet' failed, error 1004, that you're getting is because the sheet with the button on it doesn't have a range named "Result". Most (maybe all) properties that return an object have a default Parent object. In this case, you're using the Range property to return a Range object. Because you don't qualify the Range property, Excel uses the default.

The default Parent object can be different based on the circumstances. If your code were in a standard module, then the ActiveSheet would be the default Parent and Excel would try to resolve ActiveSheet.Range("Result"). Your code is in a sheet's class module (the sheet with the button on it). When the unqualified reference is used there, the default Parent is the sheet that's attached to that module. In this case they're the same because the sheet has to be active to click the button, but that isn't always the case.

When Excel gives the error that includes text like '_Object' (yours said '_Worksheet') it's always referring to the default Parent object - the underscore gives that away. Generally the way to fix that is to qualify the reference by being explicit about the parent. But in the case of selecting and activating when you don't need to, it's better to just refactor the code.

Here's one way to write your code without any selecting or activating.

Private Sub cmdRecord_Click()

    Dim shSource As Worksheet
    Dim shDest As Worksheet
    Dim rNext As Range

    'Me refers to the sheet whose class module you're in
    'Me.Parent refers to the workbook
    Set shSource = Me.Parent.Worksheets("BxWsn Simulation")
    Set shDest = Me.Parent.Worksheets("Reslt Record")

    Set rNext = shDest.Cells(shDest.Rows.Count, 1).End(xlUp).Offset(1, 0)

    shSource.Range("Result").Copy
    rNext.PasteSpecial xlPasteFormulasAndNumberFormats

    Application.CutCopyMode = False

End Sub

When I'm in a class module, like the sheet's class module that you're working in, I always try to do things in terms of that class. So I use Me.Parent instead of ActiveWorkbook. It makes the code more portable and prevents unexpected problems when things change.

I'm sure the code you have now runs in milliseconds, so you may not care, but avoiding selecting will definitely speed up your code and you don't have to set ScreenUpdating. That may become important as your code grows or in a different situation.