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.
