+ Reply to Thread
Results 1 to 4 of 4

Error when Renaming and Moving Position of Worksheet

  1. #1
    Registered User
    Join Date
    11-03-2004
    Posts
    75

    Question Error when Renaming and Moving Position of Worksheet

    Getting a "RUNTIME ERROR 1004" "Method 'Worksheets' of object '_Global' failed" error that I'm not sure how to resolve?
    It occurs early in the code at this point (highlights this line in yellow)

    Please Login or Register  to view this content.
    SCENARIO:
    The workbook arrives with a single sheet called "Sheet1" (this is a report that has a lot of garbage that the code will clean up)
    The code first preserves that original report by:
    1- creating a duplicate copy of it
    2- renaming one of those sheets "Original Rpt"
    3- places the new sheet to the left of that "Original Rpt" (preserved sheet)

    Here's a snippet from the beginning of the code:
    Please Login or Register  to view this content.
    Hoping someone sees what's wrong?
    Thank you

  2. #2
    Valued Forum Contributor ranman256's Avatar
    Join Date
    07-29-2012
    Location
    Kentucky
    MS-Off Ver
    Excel 2003
    Posts
    1,189

    Re: Error when Renaming and Moving Position of Worksheet

    instead of names ,try using sheet#
    Worksheets("Sheet1").Copy After:=Worksheets(1)

  3. #3
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2411
    Posts
    26,673

    Re: Error when Renaming and Moving Position of Worksheet

    I don't see a problem with that line of code by itself. I tried it and it worked fine. It might help to have all of your code, not just a snippet, including the code that calls CleanUp. Are you calling CleanUp in a loop? Is it possible that Sheet1 is hidden?
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  4. #4
    Registered User
    Join Date
    11-03-2004
    Posts
    75

    Re: Error when Renaming and Moving Position of Worksheet

    RANMAN256 - Thanks so much for the expeditious response -- I shared it with the person near me with the issue and they're reporting it seems to have corrected the issue and you've saved the day!
    Thanks greatly!

    6STRINGJAZZER: -- I know it was odd -- as when I ran it on a diff computer -- it did not error out --
    but on their computer it errored out every time! (must be something to do with some preconfigured setting - dunno) -

    RANMAN256 - I'm adding to your REPUTATION points as well for the solution! THANKS AGAIN!!

+ 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. [SOLVED] moving to next worksheet (Compile Error: Else without If)
    By mally91 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-25-2017, 08:31 AM
  2. [SOLVED] Renaming worksheet error handler
    By Aaron092 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-14-2015, 11:29 AM
  3. [SOLVED] Renaming worksheet based on what is contained in one cell conteant shows a syntax error
    By AB33 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-07-2012, 05:18 PM
  4. Copying and moving files - or just renaming?
    By AnthonyWB in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 04-09-2010, 10:49 AM
  5. renaming worksheet and moving data based on date
    By Dangermouse1977 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-10-2010, 11:00 PM
  6. [SOLVED] Renaming Worksheet: Error message: "Reserved Name"
    By thank you! in forum Excel General
    Replies: 1
    Last Post: 09-29-2005, 01:05 PM
  7. [SOLVED] Renaming Worksheet Error
    By bry32 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-09-2005, 06:06 PM

Tags for this Thread

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