+ Reply to Thread
Results 1 to 8 of 8

Select Method of Range Class Failed

  1. #1
    Registered User
    Join Date
    06-02-2010
    Location
    Hometown, Texas
    MS-Off Ver
    Excel 2003
    Posts
    23

    Select Method of Range Class Failed

    I have had a lot of issues with the 'Select method of Range class failed', which is the 1004 error. I have seen several responses to this error from various sources. The main reason I get this, is due to the fact that I record a lot of my macros, which include shifting from sheet to sheet, cutting and copying, etc............

    Rather than recording the macro, how would a programmer enter these functions into VBA properly. I have been told not to use the Select or Activate in code, but I am not sure how to get around this. If I need to copy and range from one sheet to another, what is the proper way to do this in code?

    S/T
    Last edited by Screamtruth; 06-07-2010 at 07:04 AM.
    Of all tyrannies, a tyranny exercised "for the good of its victims" may be the most oppressive.
    --C. S. Lewis

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Select Method of Range Class Failed

    Post the macro/workbook that produces the error. Highlight the line where the macro stops running.
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    06-02-2010
    Location
    Hometown, Texas
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Select Method of Range Class Failed

    This is a small section of the code; the entire sub is quite large, since I recorded quite a bit.
    Please Login or Register  to view this content.
    I got the error at the 'Sheets("DHPT01Collate").Range("A1").Select'.

    I get them all over this entire macro, and always at the range selections.

  4. #4
    Forum Expert contaminated's Avatar
    Join Date
    05-07-2009
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Excel 2013
    Posts
    1,430

    Re: Select Method of Range Class Failed

    IMO this error occurs because you try to select a range on a specified sheet without activationg it first. Try this instead

    Please Login or Register  to view this content.
    Люди, питающие благие намерения, как раз и становятся чудовищами.

    Regards, ?Born in USSR?
    Vusal M Dadashev

    Baku, Azerbaijan

  5. #5
    Registered User
    Join Date
    12-10-2009
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    34

    Re: Select Method of Range Class Failed

    unfortunately its not possible to select a cell in this way without activating the worksheet. But there is no real reason in most cases to select, activate and visually take the steps to move around the worksheets. For example to copy data you can use:

    Please Login or Register  to view this content.
    This will efficiently copy data from Sheet1 to Sheet2.

  6. #6
    Registered User
    Join Date
    06-02-2010
    Location
    Hometown, Texas
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Select Method of Range Class Failed

    In the above case, I see how the copy is completed, but in my code, I have times where I select a range and use auto filter. In this case, would I use select, or just use:

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    12-10-2009
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    34

    Re: Select Method of Range Class Failed

    Please Login or Register  to view this content.
    removed a double AutoFilter. No need to select the area as you can reference it using the worksheet object.

  8. #8
    Registered User
    Join Date
    06-02-2010
    Location
    Hometown, Texas
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Select Method of Range Class Failed

    I believe I just became a more proficient programmer (A VERY novice one) after this thread.

    Thank you for the input.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1