+ Reply to Thread
Results 1 to 9 of 9

Change Sheet Name

  1. #1
    Registered User
    Join Date
    09-15-2009
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2003
    Posts
    33

    Smile Change Sheet Name

    I am trying to update my sheet name based on a reference to cell a27 in the worksheet. Only thing is I don't want it to affect all tabs, just those in between my bookends aptly named first and last. I have tried working this on my own, stringing bits of already existing macros in my book, but as I am a novice (that's being generous) it is clearly not working. Can someone take a look below and advise where I am going wrong? I know I really suck at this so don't laugh too hard.


    Please Login or Register  to view this content.
    Also if anyone can offer any recommended reading on programming that would be most kind. Thanks a bunch.

    Warmest Regards-
    Kiki
    Last edited by mrsogmax76; 10-04-2009 at 12:55 AM. Reason: Solved

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Change Sheet Name

    Not entirely sure I follow but perhaps:

    Please Login or Register  to view this content.
    Note: sheet names must be unique trying to rename a sheet the same as another will in the above generate an unhandled error (deliberately left as such).

  3. #3
    Registered User
    Join Date
    09-15-2009
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2003
    Posts
    33

    Re: Change Sheet Name

    Hi DonkeyOte,

    Thanks for helping me again, but I am getting an error at Then .Name = .Range("A27").Value. Any suggestions?

    Thanks-

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Change Sheet Name

    You can right-click on the sheet tabs where you want this to occur, select VIEW CODE, and paste in this sheet-event macro into each sheet.
    Please Login or Register  to view this content.
    This will change the sheet immediately anytime you type a new value into the cell A27 on any sheet you inserted that code into.

    NOTE: If A27 changes its value by formula, then this wouldn't work.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Change Sheet Name

    Thanks for helping me again, but I am getting an error at Then .Name = .Range("A27").Value. Any suggestions?
    Can you be more specific with regard to the actual error ?

    As stated have you ensured no other sheet exists in the file with the value of A27 ? Can you provide the value in A27 - there are certain restrictions when it comes to naming sheets in terms of what is valid and what is not.

  6. #6
    Registered User
    Join Date
    09-15-2009
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2003
    Posts
    33

    Re: Change Sheet Name

    Currently default for a27 is set to "Current Invoice", but there is a list of additional names to choose from.

    If there is a need to duplicate the sheet, then I want the sheet name to reflect the activity being billed.
    Here is the way I envision it (if possible):

    * Biller duplicates sheet when multiple activities on the same contract are being billed
    * The duplicated sheet when copied defualts to "Current Invoice(2)" becuase that's just what excel does (can't have two sheets with same name"
    * Biller updates A27 on both invoices to reflect activity name
    * Clicks on an "activation button"
    * Tab names change to reflect billed activity

    I hope that helps lends some clarity to what I would like the code to do. I sure hope you can get it to work.
    Thanks Again...

  7. #7
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Change Sheet Name

    The _change macro I gave gives you the functionality you want, except you don't have to press a button, just type in a new value in A27.

  8. #8
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Change Sheet Name

    Quote Originally Posted by JBeaucaire View Post
    The _change macro I gave gives you the functionality you want, except you don't have to press a button, just type in a new value in A27.
    Except that it too will still debug should A27 be duplicated on another sheet which was my original point... the error could simply be ignored but this would I believe amount to disregarding the fundamental purpose of the code....hence my earlier question.

    Unfortunately, mrsogmax76 you didn't actually answer my earlier question:

    Quote Originally Posted by DO
    Can you be more specific with regard to the actual error ?
    What do you want to happen should there be multiple sheets with the same value in A27 - ie what is to be the naming convention ?
    Last edited by DonkeyOte; 10-04-2009 at 12:01 AM.

  9. #9
    Registered User
    Join Date
    09-15-2009
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2003
    Posts
    33

    Re: Change Sheet Name

    Oh yes. That did it. Thanks a million.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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