Raven Baxter won’t be the only one returning in the That’s So Raven sequel currently in the works at the Disney Channel. It looks like she’ll be bringing her best friend Chelsea Daniels with her!
Monday morning, Disney announced that Anneliese van der Pol will be reprising her role in the spin-off as both Raven and Chelsea join forces post-divorce to raise their kids together. In fact, the first episode of the new series sees Chelsea and her son, Levi, move in with Raven and her kids, Nia and Booker.
“The pairing of Raven-Symone and Anneliese is comedy gold,” Disney said in a statement. “They each are skillful performers and there’s a fantastic comedic chemistry between them. The two women are dear friends in real life too and are both thrilled to soon be working together again.”
Much like the original show, the Raven sequel will focus on Raven’s kids — and one of them has inherited her ability to glimpse the future. However, based on Levi’s character break down, which says he’s “not above tricking his loved ones to get what he wants” but also “fiercely protective” of anyone else who tries to do so, it seems like he might not be as good a friend as his mother was to Raven.
Since appearing in all 100 episodes of That’s So Raven, van der Pol has made her Broadway debut as Belle in Disney’s Beauty and the Beast and played the role of Kathy in Off-Broadway’s Vanities.
Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Dynamic Worksheet Tab Names.
You probably already know that you can change the name of a worksheet tab by double-clicking on the tab and providing a new name. What if you want to do it dynamically, however? What if you want to have the value in cell A1 automatically appear as the tab name?
Unfortunately, Excel doesn't provide an intrinsic function to handle this sort of task. It is a relatively simply task to develop such a function using a macro that will do the job for you. For instance, the following macro will change the tab name to the contents of A1:
There are several important items to note about this macro. First of all, there is no error checking. This means that if A1 contains a value that would be illegal for a tab name (such as nothing at all or more than 31 characters), then the macro generates an error. Second, the macro must be manually run.
What if you want a more robust macro that does check for errors and runs automatically? The result is a bit longer, but still not overly complex:
Save the workbook as an Excel template using the name BOOK.XLT in the XLStart directory. This causes the template to become your pattern for any new workbook you create.
Again save the workbook as a template in the same directory, this time using the name SHEET.XLT. This causes the template to become the pattern for any new worksheets you insert in a workbook.
Need to allow others to contribute to your Excel workbook? It's easy to do if you just share it. This tip provides an ...
When you use SUM to determine the total of a range of values, Excel doesn't really pay attention to whether the values ...
As you are new to all this, then you may have overlooked that the codes here use a slightly advanced technique, which cause a macro to do something automatically when you
A code to something along the lines of …“……Name sheet 2 with cell value in b2, Name sheet 3 with cell value in b3 and then save the whole file with cell value from b2 and b3….. “.. would be quite simple.
I am not sure what this means “..with cell value from b2 and b3..” – does that mean that the file name should be a text somehow made up of a combination of the text in two cells??
It is very unclear. You must remember that we no nothing of your project. Anything will sound OK to you, but imagine if you knew nothing about your project and someone gave them your explanation. – I expect you would be as puzzled as me as to what you actually want.. :-)
The macro is only fired when an event occurs in "his" sheet. So both your sheets must have their own macro.
The macro of this tip uses the Worksheet_SelectionChange event which is a bad choice because every time the cell pointer is moved or an entry is made in any cell the macro runs and the sheet's name is changed over and over again although it only has to be changed if the contents of A1 changes.
You can try it by inserting a Beep command before the "On Error GoTo Badname" line. Move around the sheet and enter anything in some random cells (except A1). Every time you hear the beep sound the sheet's name has been changed to the content of A1 although A1 isn't touched. What a waste !!!
The event to be used here is Worksheet_Change. The following macro is far more efficient and changes the sheet's name only if A1 is changed:
Hello, I'm new to all this and I have been learning so much and some things I get and some I don't. How would I do the following with VBA:
Name sheet 2 with cell value in b2, Name sheet 3 with cell value in b3 and then save the whole file with cell vaule from b2 and b3? I learned how
to save sheet 2 and then another macro for sheet 3, but is there an easier way to do everything with one vba?
I thought that might have been the case, as he addressed you in his post, but I was not sure where you lived, Allen,
I believe that JD confused you with me. I live in Lyman, Wyoming, which is only 260 miles or so from Blackfoot, Idaho.
You are welcome, thanks for the feedback. I am not too familiar with forms, but I think I see now where those terms are coming from, .. I guess addAccount would be a UserForm object and lName anf fName some Properties of that, such as boxes where one types stuff in.
The main problem, I expect, is that your code is not actually attempting to give the tab the name that I expect you wish to.
But this code would give a new Worksheet the name made from the values in the first two cells in the “first” worksheet:
(The item number of worksheets is counting tabs from the left. Alternatively you can refer to it by the tab string name, like
It would be fairly easy to write a Worksheet_SelectionChange type code for your "Meeting Quorum" worksheet whereby:
That is easy , and many ways to do that. The exact detail of how to do it , or rather the best way to do it, will depend on your exact "Meeting Quorum" worksheet layout.
etc.. _. Then you can upload a sample file ( change / make up names etc. to desensitize the data ). I think you would get a quick answer on what is a fairly easy task in VBA
I'm working on a spreadsheet that deals with calculating votes and whether or not a quorum is reached. So (and I've probably done this the most indirect way possible) each candidate is listed on the "Meeting Quorum" sheet and has their own respective worksheets. I want the tab names for each of the individual candidate worksheets to be updated when I change them on the "Meeting Quorum." What I currently have in place looks like this:
And for each following tab, I modify the range. This works fine, but the tab names don't immediately update; I have to click the tab, click any cell in the worksheet, and hit return. Is there any way to avoid this so it updates when I modify the referenced cell, in this case, what I input in G2?
This solution is superb for the value in specific cell.However, could you please help me with an macro, if we want to change the name of sheet using "sum of few cells" or "sum of column". would be a great help.
I know this post was from 2014. It works quite well. However, I would like to take this a bit further. For this example, I have Sheet1 with Data in 2 columns. Let's say the data is contained in Cells B1:C10. Cell A1 will contain the name I want to use to rename the tab per the suggested Macro. I want to graph the data on one or several charts that are sheets in this same Macro-enabled workbook. The charts are already created as Chart 1, and Chart 2. I would like to enter a new name for the tab Chart1 in Cell A2 (for example, New Name Chart 1") and have it automatically rename the tab "Chart1" to "New Tab Name 1" and then similarly enter a new name for the tab Chart 2 in Cell A3 (for example, New Name Chart 2) and have it dynamically rename the tab "Chart2" to "New Name Chart 2."
in order to eliminate the running of the rest of the command in case the Sheet's name already matches the A1 value.
To my opinion, it has nothing to do with the Macro which re-directs back to the HOME sheet - which produces the error.
I have a "Home" page which has links to all the tabs in the workbook. I've figured out how to ensure these work when the tab names change. Each tab has a link to the "Home" page.
The problem occurs when i am on one of the tabs, and click on a hyperlink to take me to the "Home" page. I receive an error message. If i navigate to the home page using the tabs then i don't get the same message, and if i select the cell with the link before pressing it then i don't get the message either.
Perhaps i need to bracket, or partition the code so that it does not modify other stuff in the worksheet?
Hi! I think you are the guy to help me on my macro! I would love to have A1 be a shortened tab name. I knew there is an easier way than to type it-I am trying to work smart here.
_.. makes no sense to me. I do not understand how such a code line would work, or what it is intended to do. That code line errors by me.
I modified this code to include naming of several worksheets, but it seems like I've hit a limit of 7 tabs that can be named like this, does that sound right?? Otherwise I have some other issue. Here is how I modified the code (below), which works great for up to 7 tabs, then for the 8th one it keeps giving me an error message no matter what name I try to type in. Any ideas?
I think it is difficult to get the exact info. across here that I need to fully understand the problem.
In Column A in the first Tab Worksheet of the Workbook in which you have the following code in you have your Tab Names listed, like_..
The code will Add a Worksheet for each of the dates from that list , and give the Worksheet Tab Name that date.
On the codepage for the worksheet with the master list of Client names use the "Worksheet_Change" event to create a copy of the template worksheet, name it and create the hyperlink to it. It obviously only needs to operate on one cell at a time rather than the whole list.
You'll need to cope this the situation if someone modifies an existing Client (e.g if at a later date a spelling mistake is corrected.That is Change the Tab Name and hyperlink.
I'm hoping to store all of the tab names in a sheet, let's call that sheet "Tab Names". I currently have sheets for each two week pay period in a year, and the name for each sheet is the pay day (ex: January 7). In the "Tab Names" sheet, I'll have every pay day listed for a given year (ex, January 7, January 21, etc.). I'd like to run the macro to change all the sheets names to the pay day in the "Tab Names" sheet. This will make it easier every year (as pay days change) to rename the sheets.
Sorry for my late response. I ended up doing just as you suggested Barry. After I created each individual tab and renamed each tab to the clients name. I went on my Master and each name in Column A 1 I inserted a Hyperlink using place in this document option.
Is there a way to make it become Automated when another person would add a client to the master document in column A 1?
Is there a way to get the name of a tab from a different sheet that has a tab name that won't change?
There is another tip on this website which I think will do virtually all that you want with just a couple of minor changes. You'll probably find what you are looking for there.
I would then create a hyperlink on the list of Clients which when clicked would take you straight to the Clients worksheet.
There are some caveats here if you have Clients with the same name e.g. two John Smith's for instance. Excel requires worksheets to have unique names. The code will error out as it stands which is not the most elegant way of handling this.
I need help here as well. I will assume what you all have discussed in these commits will satisfy what I need.
If what I am trying to understand in the comments above, I should be able to create with the Macros above?
I will have one master list which list all of my clients and I would like to be able to click on a client on this master list and it take me directly to this page in the Excel work book. The number of clients will be over 100 which is why I am asking if I can make the excel work book easy access to an individual page.
I am reading that I will need to go to my XL start up. However i am having trouble location this as well.
I know this is a really old post, but I used this code and it worked great. However, after I protected my workbook (and nothing else changed), I keep getting the error as if I had renamed a tab with too many characters.
Say in Excel, in Column D, I have a list of 26 teachers names (D1, D2, D3, etc). Is it possible to have excel copy an existing worksheet for each of the names listed in column D (and also name the copied sheet accordingly)?
I'm not sure quite what you mean by not "disturbing" other worksheets, I assume you mean leaving them in the same relative positions.
The code I gave on 8th July 2016 will change the tab name of a different sheet when invoked. To invoke it automatically whenever there is a change in the cell of the master sheet you just need to invoke the macro using the "Worksheet_Change" event on that sheet, you may or may not want to qualify whether or not the cell that changed is relevant to the tab name or not first.
If in Summary sheet im going add one new name like Ex(Set2). with tat name one new sheet has to create in that same workbook without disturbing my other working sheets.
Hello, This is my first time coding. I am using the code in the article and works well when it is used in the same tab. How can I change the name of the tab when a cell changes in another tab? I am sure it is really simple. I would appreciate your help.
What you want is tricky, as there isn't an event that is triggered when you change the tab name of a worksheet.
I would tackle this by recording the worksheets name when it is activated (and when the workbook is opened), and then when deactivating a worksheet check to see if the name has changed, and, if so, update your master list. You'll probably need to do this also just before saving/closing a workbook. If the hyperlink is to the codename of the worksheet then the hyperlink will not have to be changed.
There will be a period of time when the tab name will not have a corresponding entry in the master list.This could affect the operation of other macros.
With a small adaptation, the same routine could be used to create your master list in the first place.
I'm facing an issue similar to Nathan and Stuart below. I have a spreadsheet with 530+ sheets with names that consist of 7 numbers that sometimes have an "A" at the end as well (i.e. 0507171 or 0507171A). What I would like to do is link the names of each sheet to a master list located on a sheet near the beginning of the workbook so that whenever these names are updated, the name of the sheet will automatically updated.
Additionally, all of the items in the master list are hyperlinked to their corresponding sheet, so if possible I would like to have the hyperlinks automatically update as well if the name of a sheet were to change.
I've been working off of some of the code you posted below, but so far haven't been able to get the results I would like. I'm almost pretty new the using VBA so this is a bit of a new challenge for me! Any help is greatly appreciated, thanks!
The code I published on 8th July will if you add a new name to the list on the Master List, then subsequently run the code will create a new worksheet with that new name.
The code will not delete a worksheet if the entry in the Master list is deleted (this was not part of the original requirement).
It's not the most elegant or efficient way of doing it as it attempts to create a new worksheet for each entry in the Master list even though the sheet already exists especially if there are lots of entries in the Master list (it deletes the new sheet having found a sheet with that name already exists). A better way would be to create a new worksheet and try to rename it for each entry in the Master List; if successful create a new worksheet and carry on. At the end delete the remaining new worksheet that didn't get renamed.
I like the code, but how can I replicate this if I add a new tab. Right now I would have to cut and paste for each new tab I add.
Yes. You can name a tab based on a cell value on any other worksheet, or even a cell in a worksheet in another workbook.
The following code will do what you want, I think. It takes the value in column A in the master list and creates a new worksheet with this name and create a hyperlink to it from the master list. It then also creates a formula to link the data in columns on the master list to cells on the new worksheet (you might want to change these to suit your requirements) and also links the column header label.
Note: if any name in column A is an invalid Tab Name or a duplicate Tab Name then that line entry is skipped and no worksheet is created for it. You can tell which these are on the master list as the entries will not be hyperlinked.
Thanks for the information. I can reduce the name size for the medications so that they are less than 31 characters (for the tab). I can link the other data and am looking for a method to automate naming the tabs.
The VBA code to do this is relatively straightforward, but I foresee some issues with the restrictions that Excel places on the names that tabs can have; specifically the maximum length is 31 characters and the following punctuation cannot be used , / , * , ? , : , [ , ]. Truncating names to 31 characters could throw up conflicts where a tab name potentially could be duplicated (Excel will not allow this).
Navigating around such large workbooks can be a bit of a nightmare so I would strongly advise creating hyperlinks to each worksheet from the Master worksheet.
I have a spreadsheet that contains a list of 130 medications and want to create additional tabs (130) that list the medication name as the name/title of the tab. Also, each row of the master list, contains information about the medication that I would like to add in specific cells of the medication-specific tab. Are both of these possible with VBA?
Put the code below onto the "ThisWorkbook" codepage. What this code does is whenever the cell J1 is edited on any worksheet the macro will run through every worksheet and update the worksheets tab name.
The date that I wish to set is a formula referencing a Master worksheet in the same workbook and as such the cells in the individual worksheets change as the date changes in the master (so that I can update the workbook easily for each financial year) The only problem I am having is I have been changing all 52 weeks (52 tabs) of the year manually which is a real pain in the arse at the moment.
I am not sure why is not working correctly, in fact, I'm not sure why it is working at all - the "Worksheet_SelectionChange" event shouldn't work from the "ThisWorkbook" codepage. The code you pasted below would need to be pasted into the codepage of every worksheet to work. However, using the "Workbook_SheetChange" event will achieve the desired effect.
I cannot see that multiple sheets are changed other than by a ripple through of a calculation from one sheet onto every other affected sheet, but testing this doesn't trigger the Workbook_SheetChange event, the Woksheet_SelectionChange or even the Worksheet_Change events on dependent sheets. So something else is at play here. If you'd like to email me your workbook I'd happily take a look.
I've also found a bug in Excel2010 which results in a tab name not being updated when a cell changes. If whilst in editing cell J1 the active sheet is changed, the data in the cell is entered/updated but the SheetChange event isn't triggered resulting the tab name remaining unchanged.
Basically I'm trying to dynamically rename 50 worksheets to the weekending date based on a cell that is located in each worksheet.
The cell location that i'm trying to reference does not change between sheets, just the sheet number (e.g. the cell is J1 for each sheet).
I was able to use this code for one sheet but it automatically updated the rest of the sheets to be two days ahead
i.e. cell A1 contains the formula =weeknum(A2). I want to name the tab whatever week number is returned to cell A1 but using your code I get an error message.
Essentially I have Company Name in cell D11 and Project Name in D13. I was hoping to have the sheet rename to include both so that it displays as: "Company - Project"
Please note though after you've added one new worksheet the macro will fail, as the macro will attempt to name the next new sheet with the sheet name of the first new sheet which is not allowed. So the value in cell A1 on Sheet1 must be changed before another new sheet is added or something needs to be added to the name to make it unique.
I am trying to write a code to add a new sheet in excel and re-name the sheet with value of the Cell of the first sheet.
I would like to create a macro that assigns worksheet name based on two cells and define where to save it as it will be save to a cloud based dropbox. I can place the template for each worksheet on the individual tablets but don't want them stored locally on the device. All devices will have the same files and ideally cell based naming with date and save location would be most beneficial. Any assistance would be greatly appreciated
Using this VBN I can change one sheet tab name,Please tell how can change next pages also by using VBN.I have some words befor other sheet also.
In the same name another sheet is there then, copy cell 39 of that sheet and paste to cell B2 of sheet 1 and contuning this activity until all sheets covered
Say in Excel, in Column D, I have a list of 26 teachers names (D1, D2, D3, etc). Is it possible to have excel create an individual worksheet for each of the names listed in column D instead of having to double-click each worksheet and rename?
How can I implement this code for just one excel file and run it on an ad-hoc basis to 'update' the tab names to match cell A1 in each worksheet?
Is it possible to link 2 cells in the tab name. For e.g. cell A1 says "1" and cell A2 says "ABC", then in the Tab name I would like to see "1 ABC". Please advise what modification should I do in the code.
Thanks. This worked. However the message box warning did not come up when the referenced cell was blank.
Your "people" comment implies that there are several Users. Therefore it would make sense to put some controls what "people" can do.
Personally, in this situation I would protect the Workbook structure and put all worksheet naming under macro control (you could even restrict what certain Users can do as well). You might want to restrict whether "people" can delete worksheets, change formulas, delete rows/column, etc. etc.
I agree to your point but, people will keep changing the names in the respective cell(renaming the tab or in case of spelling mistake etc). And of-course the sheet/ workbook will be protected. so what do you suggest?
What you ask for is entirely possible using the worksheets' codename (and would be a better way for several reasons), but it requires a lot of extra code because as it is not straightforward to create a Worksheet object from the text codename for a worksheet.
Also this can be very confusing for a User who in normal circumstance does not have access to the codenames of individual sheets. For instance re-opening a workbook in which all the sheets have previously been renamed, adding a new worksheet will be given a sheet name of "Sheet1" however, its codename WILL be different (unless the sheet with codename "Sheet1" previously has been deleted) such as "Sheet4". It is possible to get around this but by refreshing the names table but this cannot be done automatically as Excel doesn't have an event on Worksheet creation, so would have to be on a timer which periodically checks for new worksheets.
This is a "horses for courses" if you want something which is very robust/bomb-proof then it'll take a lot of extra coding but it may be necessary if the workbook has several inexperienced Users who are adding/deleting worksheets. You might even protect the Workbook structure and only allow new worksheet creation under macro control - how far do you want to go? If it's just yourself then something simpler (as we already have) might be adequate.
Go ahead and disregard my last comment. Out of sheer luck, I found another comment asking my question and figured it out!
Hi. I'm using the second code you've posted, and it works perfectly fine when I'm only entering in text. BUT, when I try to enter in a date (form: 23-JUN-15... well, any form really, I think it's the numbers), I get the "Bad name" command.
Note also when you close and reopen the workbook when you add new worksheets it will name them "Sheet1", "Sheet2", etc etc. The macro if run will try to rename these sheets with the names in the list but fail as the name will already exist. You only need to put the new name into the list for "Sheet1" for it to be renamed.
Hi Berry, in addition to my previous query. It is working fine, the only issue is you have to create a button where you can assign that specific macro so, i was wondering is there is any way we execute this macro without any button. like when i type the names in the cells, and when a move to next cell, it automatically renames the sheets tab, something like that.
There are several tips on this website for combining multiple worksheets into one. But ultimately it depends upon your precise requirements, and he specifics of your workbooks.
i wanted to ask you one more thing, is it possible to compile multiple sheets in 1 excel file. for eg: I have 5 separate sheets (same copy) now i want to compile all the data from that 5 sheets to another excel workbook. Master sheet, with same format of-course? how complicated is it?
Sorry the attempt at the list lost most of its formatting - please view the screen shot on this link: http://tinyurl.com/on4pd6v
Put the following macro on the same CodePage as the above list (right-click on the sheet tab and select "View Code").
Note: as before if a sheet with the new name already exists or the name is illegal, then it will be skipped and the sheet will remain with its existing name.Therefore none of the new names should be the same as ANY existing sheet name.
Hi Berry, I appreciate your help but both the macros are not working, i tried. please help me as i am new in writing macros.
I would prefer a macro in which i would specifically mention the names of the sheets whom i want to rename. pl help.
The following two macros will either rename up to 12 sheets if their existing names start with "Sheet", or in the second macro will rename all selected sheets up to a maximum of 12. In both cases the names will be taken from the list on the current active sheet that reside in cells A1 to A12. If a sheet with the same name already exists then it will be skipped, and the name not used. Also if a particular name is illegal it will be skipped and that worksheet will be left with its original name.
In both cases the macro will end after 12 sheets have been renamed, or all of worksheets have been renamed or if the macro runs out of names.
Barry - Thank you. I used your comment and did it simply by creating the following macro in my PERSONAL.XLSB and assigning it to CTRL+SHIT+R:
I'm not real knowledgeable about these things but can usually incorporate others' advice. Thanks again.
You could select the sheets that you want to rename, then have the macro step through each selected sheet in turn and rename it from the list, or the macro could starting at the leftmost sheet if has not been renamed (i.e. its current name doesn't start with "Sheet") and rename the first 12 using the names from the list.
Hi, I guess it will be random because i have more then 40 sheets. So the 12 sheets are the selected ones whom i want to rename.its in left side but other sheets are also available on left which i don't want to rename. pl help.
What you want is quite easy to do but before writing any code you need to decide on what the criteria is to assign which name to which worksheet.
already does what you want - it takes the leftmost 31 characters if the target cell's content is longer than 31 chars, or the whole value if shorter than or equal to 31.
out of 20 sheets I want to rename only 12 sheets from only 1 sheet. (when i put values in the cells(A1:A12) in sheet 1, that 12 sheets shall get renamed accordingly. is it possible?
I like e basic macro given in the beginning, and I realize that Excel creates a 31 character limitation to tab names. Some of my A1 values will be >31 characters.
I assume there should also be logic that does something differently if a worksheet already contains the name, such as adding an incremental number to the end.
Then a tab for each Sales Person (The tab name reads Smith, L John. There are 80 sales people) the tab includes:
Every month I have to split up files into 9 new workbooks, 1 for each region manager including only their sales people.
Use the article macro to change tab name to region and then I am stuck... Any help would be really appreciated.
There will be a different dates in each sheet in cell A1 to change each tab in multiple worksheets that have tab names Daily Report (1), Daily Report (2), Daily Report (3), etc up to (20) sheets
I am taking an excel class and when i do the skill based exams, when i click on A1 it automatically goes to A2, but if i am on any other worksheet it works fine. I did live chat with pearson, didnt help, any suggestions
I have a requirement where in an excel sheet the first tab data should be populated with the data uploaded in other tab..
And i want only desired columns, and some aggregations on columns to be shown on the first tab, don't want as it is populated with the referred data of other tab.
Thank you for the tip. I am needing a more robust macro for the workbook that I am creating. I am creating a tracking workbook that will have facility names in an array of cells say A3:A15 on a 'Summary' page. I would like for the names to appear as they are in those cells in corresponding order on the worksheets in the book.
If at all possible I would like it just to have 1 button to run it and then it populate all of the names on the tabs.
Marta: It is not a limit of the routine, but a limit of Excel. Worksheet names cannot be any longer than 31 characters, and there is no way to change that.
Hello I have a worksheet with a list with all the names to rename 400 Worksheets . I have a vba routine to rename all of them However its fails because the routine does not accept names with are more 31 characters.
I really want to be able to code my tabs based on a lookup table and Google Sheets doesn't support Macros. I could use a sharable worksheet but I'm still not getting this Macro concept with Excel.
This is very easily done once you appreciate that the formula you are putting in the various cells is just a string. I note that you've commented out the lines of code using the InputBox function which is how you'd assign a name for a sheet.
my question is there any way that the SheetName (100.RTR'!) can be change/replace by putting one input box? and whatever the input box value all of the sheetname (100.RTR'!) will be change/replace with the input box value.
I have a workbook that has multiple sheets in it that store data and compile rep information on a master sheet. The last 18 tabs of the workbook display the compiled rep information for each rep to print. I want only the last 18 sheets (rep tabs) to change tab names as I change rep information on the master sheet (the information feeds into each rep sheet in cell A1), the other tabs for the data and the master sheet do not need to change tab names. I am able to change the tab name using the code above as long as I click on each rep tab individually and then click on any cell within that sheet. However, each rep sheet also has a hyperlink back to the master sheet and when I click on the hyper link on any rep sheet I get the bad name error. Putting the name of the master sheet in A1 of the master sheet does not seem to help and that would not be a solutions for the other non-rep sheets. Is there a way to make the coding above only affect the last 18 (rep) sheets and avoid the error when hyperlinking back to the master or any other sheet? Also, is there a way to make the name changes happen automatically when I update the rep names in the master sheet without having to then click on each tab individually to make the change?
This code name the sheet tabs from left to right. It checks to see if there is at least 7 sheets otherwise it exits. There is also code that causes the routine to exit if A1 hasn't changed, or if A1 doesn't contain a valid date (i.e. a value that Excel interprets as a date).
Note: this macro must be placed on the code page for the worksheet which has the date in cell A1, this doesn't have to be the first sheet (i.e. the leftmost tab) but will name the tabs from the leftmost. The code could be adapted to name tabs from the current position then to the right, and if necessary adding extra sheets if required.
I love what you've done. However, is there a way to get multiple tabs to be renamed based on a cell value on the first tab? Essentially, I change the date on the first tab, and I have 7 tabs that need to be renamed with the subsequent dates for 1 week. I already have cells within the remaining tabs linked to the first tab, and I entered your code (above) for each tab.
However, I have to manually click on each tab and click anywhere on the sheet to get the names to update. I'd like to bypass that step if possible. Help? Thanks.
If it finds a date in the referenced cell then it formats the name to be put in the Tab to have underscores otherwise it just uses the contents of the cell.
I've created a time sheet to track my comp hours. Cell D1 has the date the pay period begins. Cell F1 contains the formula =IF(D1>1,D1+13,"")for the last day of the pay period.
I used the longer macro above, but my problem is the date format. I've set the category to Date and mm/dd/yyyy. The / character is not allowed on a worksheet tab so I get an error message.
Until now I have manually changed the tab name to mm_dd_yy but it would be great if the tab would auto name using the date in F1. Is there a way to have the macro change the / character to _ ?
Try this one line of code to be added to your existing sheet creation macro - probably immediately after the bit that creates each sheet.
Try opening VB then selecting "Insert" then "Module" and put the code into there so it applies to all sheets
I wondered if it is possible to do this the other way round and have the cell change to the name on the sheet tab. I have a dated sheet macro that will create as many days as required based on a master template - however, I can't get the day to show up automatically on the sheet - but it does on the tab - any help gratefully received.
I hae a formula =MATCH($B$3,Jan!$F:$F,0)That I want to slect sheet name - in this case - "Jan" automatically and column range - in this case - " F:F " as well.
Great help! One small question.. I copied the code and followed your instructions to have an automated change in every new workbook that I open. It worked. I change A1 to A2 (to have the contents of A2 as a tabname) and it doesn't anymore.. What can be the reason for this?
Please disregard my last comment. I was using Excel 2007. I found the update to this instruction set for that version of Excel and it worked perfectly.
I tried to do as per the instruction but the sheet is not getting renamed as per A1.... Kindly elaborate.