+ Reply to Thread
Results 1 to 15 of 15

Change worksheet name macro

  1. 09-30-2014, 10:34 AM #1
    billgyrotech
    billgyrotech is offline
    Banned User!
    Join Date
    03-06-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    295

    Change worksheet name macro

    My workbook contains invoices with the first worksheet being the customer list. From the second worksheet on the worksheet names increment by one starting at 20001. I tried to make a macro that will do this automatically but there seems to be errors with my code:

    Please Login or Register  to view this content.
    The numbers are not consistently adding one to each invoice and I haven't figured out how to skip the first worksheet so that it remains named 'Customers'.

    Can someone please help?

    Thank you very much.
    Last edited by alansidman; 09-30-2014 at 10:41 AM. Reason: code tags
    Reply With QuoteRegister To Reply
  2. 09-30-2014, 10:39 AM #2
    walruseggman
    walruseggman is offline
    Forum Expert
    Join Date
    08-28-2014
    Location
    Texas, USA
    MS-Off Ver
    2016
    Posts
    1,796

    Re: Change worksheet name macro 

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  3. 09-30-2014, 10:40 AM #3
    stnkynts
    stnkynts is offline
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Change worksheet name macro 

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  4. 09-30-2014, 10:41 AM #4
    alansidman
    alansidman is online now
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2405 Win 11 Home 64 Bit
    Posts
    23,944

    Re: Change worksheet name macro

    Code Tags Added
    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



    (Because you are new to the forum, I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
    Reply With QuoteRegister To Reply
  5. 09-30-2014, 10:47 AM #5
    billgyrotech
    billgyrotech is offline
    Banned User!
    Join Date
    03-06-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    295

    Re: Change worksheet name macro

    I get weird ranges now:

    2001-2009 then skips to 20010-20099 then 200100-200201
    Reply With QuoteRegister To Reply
  6. 09-30-2014, 10:49 AM #6
    Norie
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Change worksheet name macro

    Try using 2000+i instead of "200" & i.
    Please Login or Register  to view this content.
    Last edited by Norie; 09-30-2014 at 10:54 AM.
    If posting code please use code tags, see here.
    Reply With QuoteRegister To Reply
  7. 09-30-2014, 10:49 AM #7
    JOHN H. DAVIS
    JOHN H. DAVIS is offline
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Change worksheet name macro

    Looks like stnkynts beat me too it.

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  8. 09-30-2014, 10:52 AM #8
    walruseggman
    walruseggman is offline
    Forum Expert
    Join Date
    08-28-2014
    Location
    Texas, USA
    MS-Off Ver
    2016
    Posts
    1,796

    Re: Change worksheet name macro

    They're not weird ranges, that's what it should do with the way your code is written. If you want it to start at 2000 and go up from there (into the 3000s, 4000s, etc.) then 

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  9. 09-30-2014, 10:58 AM #9
    billgyrotech
    billgyrotech is offline
    Banned User!
    Join Date
    03-06-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    295

    Re: Change worksheet name macro

    This code works great thank you very much. I just had to change the '2001' to '20001' to have my numbers start at 20001.

    I really appreciate all the help guys.

    I have one more question please: Is there an easier way to add more worksheets if needed in the future?
    Reply With QuoteRegister To Reply
  10. 09-30-2014, 11:02 AM #10
    stnkynts
    stnkynts is offline
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Change worksheet name macro

    I have one more question please: Is there an easier way to add more worksheets if needed in the future?
    Are you wanting to add the sheets within the loop or independently? Adding a sheet via macro is simply

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  11. 09-30-2014, 11:04 AM #11
    billgyrotech
    billgyrotech is offline
    Banned User!
    Join Date
    03-06-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    295

    Re: Change worksheet name macro

    Sorry I want to add more sheets with the same formatting from the last worksheet in the workbook. If I do this before it is filled out then all the new worksheets will be ready.
    Reply With QuoteRegister To Reply
  12. 09-30-2014, 11:11 AM #12
    stnkynts
    stnkynts is offline
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Change worksheet name macro

    I am still not sure what you mean.
    Reply With QuoteRegister To Reply
  13. 09-30-2014, 11:18 AM #13
    billgyrotech
    billgyrotech is offline
    Banned User!
    Join Date
    03-06-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    295

    Re: Change worksheet name macro

    If I keep the last worksheet blank and copy it how can I paste say an additional 100 sheets?
    Reply With QuoteRegister To Reply
  14. 09-30-2014, 11:26 AM #14
    Vikas_Gautam
    Vikas_Gautam is offline
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Change worksheet name macro

    Try this..
    Please Login or Register  to view this content.
    Untested though..
    Regards,
    Vikas Gautam
    Excel-buzz.blogspot.com
    Excel is not a matter of Experience, its a matter of Application.
    Say Thanks, Click * Add Reputation
    Reply With QuoteRegister To Reply
  15. 09-30-2014, 11:28 AM #15
    billgyrotech
    billgyrotech is offline
    Banned User!
    Join Date
    03-06-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    295

    Re: Change worksheet name macro

    Thanks but the reply from walruseggman worked great. Is there a way to submit that this was solved on here?
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Macro to add worksheet - worksheet names change
    By PostID in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-13-2013, 01:05 PM
  2. [SOLVED] Worksheet change macro help
    By taken31 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-11-2013, 03:18 PM
  3. [SOLVED] Worksheet change macro on a change in a specific cell
    By dzuspann in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-07-2012, 04:19 PM
  4. Worksheet change macro takes too much time when run with update list macro
    By hunsnowboarder in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-01-2009, 09:40 AM
  5. Cell value change to trigger macro (worksheet change event?)
    By Neil Goldwasser in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-10-2006, 10:00 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1