+ Reply to Thread
Results 1 to 11 of 11

Run-time error '1004': Method 'Select' of object'_Worksheet' failed

  1. #1
    Registered User
    Join Date
    08-06-2013
    Location
    South Carolina
    MS-Off Ver
    Excel 2007
    Posts
    5

    Run-time error '1004': Method 'Select' of object'_Worksheet' failed

    A co-worker has been experiencing this error, for approx. 1 month. This error appears automatically, each time she opens up a particular spreadsheet.
    When I select option to "debug" - this is the code which appears:

    Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    Sheets("Template").Visible = True
    Dim mysheet As Worksheet
    For Each mysheet In Worksheets
    mysheet.Select
    mysheet.EnableSelection = xlUnlockedCells
    Next mysheet
    Sheets("Template").Visible = False
    Application.ScreenUpdating = True
    End Sub


    Is there something here that needs to be corrected?

    Thank you,

  2. #2
    Forum Contributor
    Join Date
    10-08-2010
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    386

    Re: Run-time error '1004': Method 'Select' of object'_Worksheet' failed

    A couple things, make sure there is a template sheet in the workbook. if not then this code will give an error. If it still doesnt work try the code below. If THAT doesn't work just put On Error Resume Next at the top of the code under private sub.

    Please Login or Register  to view this content.
    Last edited by GaidenFocus; 08-06-2013 at 05:04 PM.

  3. #3
    Registered User
    Join Date
    08-06-2013
    Location
    South Carolina
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Run-time error '1004': Method 'Select' of object'_Worksheet' failed

    Thank you. I will give this a try tomorrow and see what happens!

  4. #4
    Forum Contributor
    Join Date
    10-08-2010
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    386

    Re: Run-time error '1004': Method 'Select' of object'_Worksheet' failed

    I think this may have been caused by .Select. I think you need to use .Activate to specify that sheet only

  5. #5
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Run-time error '1004': Method 'Select' of object'_Worksheet' failed

    Gaiden, you should try your code with Option Explicit at the top of the module.

    Please Login or Register  to view this content.
    Entia non sunt multiplicanda sine necessitate

  6. #6
    Registered User
    Join Date
    08-06-2013
    Location
    South Carolina
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Run-time error '1004': Method 'Select' of object'_Worksheet' failed

    ok. Tried the suggestion in regards to 4:12PM post, from yesterday. And she now has a "Compile error: Invalid Next control variable reference"
    Should I try the next suggestion - remove .Select and replace with .Activate ? Or do we now have a different issue at this time?
    Here is what it looks like at this time:

    Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    Sheets("Template").Hidden = True

    For Each Sheet In Worksheets
    mysheet.Select
    mysheet.EnableSelection = xlUnlockedCells
    Next mysheet
    Sheets("Template").Hidden = False
    Application.ScreenUpdating = True
    End Sub



    Thank you,

  7. #7
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Run-time error '1004': Method 'Select' of object'_Worksheet' failed

    Please Login or Register  to view this content.
    Last edited by Solus Rankin; 08-07-2013 at 01:15 PM.

  8. #8
    Registered User
    Join Date
    08-06-2013
    Location
    South Carolina
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Run-time error '1004': Method 'Select' of object'_Worksheet' failed

    Thank you XeRo Solus - I copied your code exactly and tried again. It is still giving me the "Compile error"
    I previously had the 438 error, and this occurs when you use "mySheet" - when I removed "my" it only gives me the "Compile error"
    Not sure what this means. But, still no go. Any other suggestions?

    Thank you.

  9. #9
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Run-time error '1004': Method 'Select' of object'_Worksheet' failed

    Sorry I got too focused on your variables.

    Try:
    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    08-06-2013
    Location
    South Carolina
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Run-time error '1004': Method 'Select' of object'_Worksheet' failed

    OMG! It worked!! Thank you so much!!

  11. #11
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Run-time error '1004': Method 'Select' of object'_Worksheet' failed

    Glad to help. If you are satisfied with the solution please mark the thread as [SOLVED] using the thread tools at the top.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Run time Error 1004 Method 'Range' object '_Worksheet 'failed
    By Monica1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-24-2013, 07:42 AM
  2. [SOLVED] Runtime error 1004: Method 'Range' of object '_Worksheet' failed
    By Phil Payne in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-17-2013, 01:30 AM
  3. me too - run-time error '1004' method 'range' of object '_worksheet' failed
    By ASAFSWIS in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-07-2013, 05:14 PM
  4. [SOLVED] Name a dynamic range - Run-time error 1004 Method 'Range' of object'_Worksheet' failed
    By DavidBW in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-03-2012, 08:00 AM
  5. Receiving runtime error 1004: Method 'Range' of object '_Worksheet' failed
    By DrShocktopus in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-28-2010, 10:39 AM

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