Select method of Range class failed via VBA

Question

This is the code that I m currently working with  and I m getting this problem  I m novice at Excel and I can t figure out what s wrong   Private Sub cmdRecord Click   Sheets  BxWsn Simulation   Range  Result   Select   This is the line with the problem  as excel told me      Selection Copy     Sheets  Reslt Record   Select     Sheets  Reslt Record   Range  A5000   End xlUp  Offset 1  Select     Selection PasteSpecial Paste  xlPasteValuesAndNumberFormats  Operation             xlNone  SkipBlanks  False  Transpose  False     Sheets  CuCon Simulator   Select     Application CutCopyMode   False     Range  Improvement   Select End Sub   The error is Select method of Range class failed via VBA  Error 1004  Any ideas   ETA   So I just changed the code to   Sheets  BxWsn Simulation   Select Range  Result   Select   I believe this is what you mean by making it active   However I m still getting Method  Range  of object   Worksheet  failed  error 1004

User · Answer

This is how you get around that in an easy non-complicated way  Instead of using sheet x  range use Activesheet range  range   select

User · Answer

This worked for me   RowCounter   Sheets 3  UsedRange Rows Count   1  Sheets 1  Rows rowNum  EntireRow Copy Sheets 3  Activate Sheets 3  Cells RowCounter  1  Select Sheets 3  Paste Sheets 1  Activate

User · Answer

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

User · Answer

Here is a solution worked for me and also  I found all of the above solutions are correct  My excel model got corrupted and which is why my code  similar to this one  stopped working  Here is what worked for me and is working every time-  Calculate the workbook- Formulas- gt Calculate Now  under calculation section  Save the workbook Close and re-open the file  It was fixed and works every time

User · Answer

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

[excel] Select method of Range class failed via VBA

Examples related to excel

Examples related to vba