Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Dynamic Worksheet Tab Names

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:

Sub myTabName()
    ActiveSheet.Name = ActiveSheet.Range("A1")
End Sub

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:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Set Target = Range("A1")
    If Target = "" Then Exit Sub
    On Error GoTo Badname
    ActiveSheet.Name = Left(Target, 31)
    Exit Sub
Badname:
    MsgBox "Please revise the entry in A1." & Chr(13) _
    & "It appears to contain one or more " & Chr(13) _
    & "illegal characters." & Chr(13)
    Range("A1").Activate
End Sub

To set up this macro, follow these steps:

  1. Open a new workbook that has only one worksheet in it.
  2. Right-click the worksheet tab and select View Code from the resulting Context menu. Excel displays the VBA Editor.
  3. Paste (or type) the above macro into the code window.
  4. Close the VBA Editor.
  5. Locate the XLStart folder on your system. (Use the Windows search capabilities to locate the folder.)
  6. 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.
  7. 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.
  8. Close and restart Excel.

Now, anytime you change the value in cell A1, the worksheet tab also updates.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2145) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Dynamic Worksheet Tab Names.

Related Tips:

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

 

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 5+3 (To prevent automated submissions and spam.)
 
 
           Commenting Terms

Comments for this tip:

Barry    07 Sep 2016, 05:17
@Michael

There's is another tip on this website that addresses this issue specifically.

You'll probably find what you are looking for there.

http://excelribbon.tips.net/T013463_Creating_Worksheets_from_a_List_of_Names.html
Michael    06 Sep 2016, 05:50
I am trying to figure out if this is possible.

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)?

Thanks.
Barry    30 Jul 2016, 09:25
@Ramanathan,

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 following code will add a worksheet and position at the end of the worksheet tabs. It will then name it "New Tab Name " or whatever you decide to name it as.

Sub AddWks()
    Dim wks As Worksheet
    Dim NewTabName As String
    
    NewTabName = "New Tab Name"
    
    Set wks = Worksheets.Add(After:=Worksheets(Worksheets.Count))
    wks.Name = NewTabName

    Set wks = Nothing

End Sub

Barry    30 Jul 2016, 09:12
@Ernesto,

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.
Ramanathan    29 Jul 2016, 07:56
Hi,

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.

is there any coding or etc.,

Please help me on this
Ernesto    28 Jul 2016, 15:36
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.
Barry    23 Jul 2016, 05:34
@Scott,

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.
Scott    22 Jul 2016, 09:25
Hey Barry,

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!
Barry    20 Jul 2016, 06:25
@Terry,

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.
Terry Tewell    19 Jul 2016, 15:24
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.

Thanks
Barry    18 Jul 2016, 05:12
@Tina

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 exact implementation would depend on your specific requirements.
Tina    17 Jul 2016, 09:45
Hi,

Thanks for the information.
I am just wondering whether I can name the sheet tab as a cell value in another sheet?

Cheers.
Barry    08 Jul 2016, 06:17
@Stuart

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.

Private Sub CreateSheets()
    Dim TabName As String
    Dim Sh As Worksheet, NewSh As Worksheet, shMaster As Worksheet
    Dim rng As Range, c As Range
    Dim i As Integer
    
    'Assumes Row 1 of master list contains headers
    
        On Error Resume Next
        
        Set shMaster = ThisWorkbook.Worksheets("MasterList") 'Change this to be the Tab name of your master list
        
        Set rng = shMaster.Range(shMaster.Range("A2"), shMaster.Range("A" & Rows.Count).End(xlUp))
        
        For Each c In rng
            If c <> "" Then
                Set Sh = ThisWorkbook.Worksheets.Add(After:=Worksheets(Worksheets.Count)) 'add new worksheet
                With Sh
                    .Name = Left(c, 31)
                    If .Name <> Left(c, 31) Then 'delete new worksheet if not named correctly, then skip
                        Application.DisplayAlerts = False
                        Sh.Delete
                        Application.DisplayAlerts = True
                    Else
                        shMaster.Hyperlinks.Add Anchor:=c, Address:="", SubAddress:="'" & Trim(Sh.Name) & "'!A1" 'create hyperlink
                        i = 1
                        Do Until shMaster.Cells(1, i) = "" 'link data from master list to new worksheet
                            .Range("A" & i).Formula = "=" & shMaster.Name & "!" & Cells(1, i).Address
                            .Range("B" & i).Formula = "=" & shMaster.Name & "!" & c.Offset(0, i - 1).Address
                            i = i + 1
                        Loop
                        .Columns.AutoFit 'adjust column widths on new worksheet
                    End If
                End With
            End If
        Next c
        shMaster.Activate
End Sub
Stuart    06 Jul 2016, 09:17
Barry,

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.

Thanks

Stuart
Barry    05 Jul 2016, 05:24
@Stuart,

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.
Stuart    04 Jul 2016, 22:21
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?

Thank you.

Stuart

Barry    25 Jun 2016, 11:25
@Nathan,

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.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim TabName As String
    Dim sh As Worksheet
    
        
    If Intersect(Target, Range("J1")) Is Nothing Then Exit Sub
        On Error GoTo Badname
        For Each sh In Worksheets
            If IsDate(sh.Range("J1")) = True Then
                TabName = "Week " & Format(sh.Range("J1"), "dd-mmm-yy") 'change this line to give the precise
                                                                         'tab name format that you want
        Else
                TabName = Range("J1")
            End If
            If TabName <> "" Then
                sh.Name = Left(TabName, 31)
            End If
        Next sh
Exit Sub

Badname:
        MsgBox "Please revise the entry in J1.on worksheet: " & sh.Name _
        & Chr(13) _
        & "It appears to contain one or more " & Chr(13) _
        & "illegal characters." & Chr(13)
        Range("J1").Activate
End Sub
Nathan    23 Jun 2016, 20:55
Thanks Barry,

Can you perhaps suggest a more appropriate code then?
I just copied this from one of your comments below and hoped it would work.

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 do run excel 2010 at so that may be an issue as you mentioned?

Any help you can provide is appreciated!

Cheers,
Nathan
Barry    22 Jun 2016, 09:51
@Nathan

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.
Nathan2    21 Jun 2016, 21:35
Hi Barry,

The comment I've posted below is the macro I ran (sorry I just copy and pasted).
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

e.g. Sheet 1 -> 10/7/16 (OK!)
Sheet 2 -> 19/7/16 (Should be 17/7/16)
Sheet 3 -> etc.

Are you able to help me at all?

Regards,
Nathan
Nathan    21 Jun 2016, 21:29
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Dim TabName As String
    
    If IsDate(Range("J1")) = True Then
        TabName = Format(Range("J1"), "dd-mm-yy")
    Else
        TabName = Range("J1")
    End If
    If TabName = "" Then Exit Sub
    On Error GoTo Badname
    ActiveSheet.Name = Left(TabName, 31)
    Exit Sub
Badname:
    MsgBox "Please revise the entry in J1." _
    & Chr(13) _
    & "It appears to contain one or more " & Chr(13) _
    & "illegal characters." & Chr(13)
    Range("J1").Activate
End Sub
Barry    11 Jun 2016, 05:26
There is an error in my original code:

The line:
Set Target = Worksheets("Sheet1").Range("A1") should be changed to Set Target = sh.Range("A1")
Frank    10 Jun 2016, 05:45
Ignore me. I was running a bugged script!
Frank    07 Jun 2016, 11:02
Hello Barry.
What about using this code when naming a tab based on a cell containing a formula?
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.

Thanks for your patience.
Willy Vanhaelen    27 May 2016, 11:03
Oops, typo:
ActiveSheet.Range(D13") must be
ActiveSheet.Range("D13")
Willy Vanhaelen    27 May 2016, 11:00
@Joe B

In the fhe first macro replace:

ActiveSheet.Name = ActiveSheet.Range("A1")

with:

ActiveSheet.Name = ActiveSheet.Range("D11") & " - " & ActiveSheet.Range(D13")
Joe B    26 May 2016, 16:04
This is great!
Sorry if this has been covered already...
Is it possible to change this to include the range of two cells?
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"

Thank you in advance!
Barry    24 May 2016, 08:00
@Manu

Put the code below into the "ThisWorkbook" module.

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.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Dim Target As Range
    Set Target = Worksheets("Sheet1").Range("A1")
    If Target = "" Then Exit Sub
    On Error GoTo Badname
    ActiveSheet.Name = Left(Target, 31)
    Exit Sub
Badname:
    MsgBox "Please revise the entry in A1 ob Sheet1." & Chr(13) _
    & "It appears to be invalid " & Chr(13)
    Range("A1").Activate
End Sub
Manu    24 May 2016, 00:23
Hello,

Please help me..


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.

Ex: Sheet 1 : A1: Run
New Sheet 2, now should be renamed to value in the A1 of first sheet.

Please help!!
john burns    02 Mar 2016, 08:55
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
Babu    17 Feb 2016, 09:05
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.
thanks and Regards
Babu.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "D4" Then Me.Name = "INSPECTION - " & Target.Value
End Sub
hashir    12 Oct 2015, 08:35
In sheet 1 cell A2, contains a name.
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
Barry    16 Sep 2015, 12:54
@Patty

The following code should do what you want.

Sub CreateSheets()

   Dim ws as Worksheet, wks as Worksheet
   Dim rng as Range

   On Error Resume Next

   Set ws = Activesheet.Range("D1"). CurrentRegion
   For Each rng In ws
      Set wks = ThisWorkbook.Worksheets.Add
      Wks.Name = rng.Value
   Next rng
End Sub

where the new tab name would be invalid (too long, invalid characters) or duplicated, the tab name will not be named.
Barry    16 Sep 2015, 12:42
@Nam,

The following code should do the job:

Sub RenameTabs()
   Dim ws as Worksheet

   On Error Resume Next
   For Each ws in Worksheets
       ws.Name = ws.Range("A1")
   Next ws
End Sub

where the new tab name would invalid (too long, invalid characters) or duplicated, the tab name will not be changed, or if the sheet is a Chart Sheet.
Patty    15 Sep 2015, 09:00
I am trying to figure out if this is possible.

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?

Thanks.
NAM    11 Sep 2015, 12:37
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?
Ricky    19 Aug 2015, 13:42
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.
Thank you very much!!!
AB    04 Jul 2015, 11:16
Thanks. This worked. However the message box warning did not come up when the referenced cell was blank.
Barry    25 Jun 2015, 05:15
@Abhijet

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.
Abhijeet    24 Jun 2015, 08:32
@ Berry
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?
Barry    24 Jun 2015, 07:31
@Abhijet

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.
Abhijeet    24 Jun 2015, 05:06
@
BERRY
Hi,
Thanks berry this is working.
Can i use the actual name of the sheets instead of using the front end sheet names. for eg: my sheet names are Dist1, Dist2, Dist3... so on. But the back end names are (Sheet2, Sheet5, Sheet9, Sheet 12... so on). with this technique the sheets tabs, can be renamed whenever we want.
Jason    23 Jun 2015, 08:55
Go ahead and disregard my last comment. Out of sheer luck, I found another comment asking my question and figured it out!

Thanks again though for the article!!
Jason    23 Jun 2015, 08:49
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.

Is there something I can do to get this macro to work with dates? I'm using it for a time sheet.

Thanks ahead of time!!
Abhijeet    23 Jun 2015, 07:03
@
BERRY
Thanks berry this is working.
Can i use the actual name of the sheets instead of using the front end sheet names. for eg: my sheet names are Dist1, Dist2, Dist3... so on. But the back end names are (Sheet2, Sheet5, Sheet9, Sheet 12... so on). with this technique the sheets tabs, can be renamed whenever we want.
Barry    22 Jun 2015, 06:18
@Abhijeet

To trigger the macro as a result of a change on the worksheet is very easy

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim x As Integer
    Dim wks As Worksheet
    Dim str As String
    
    If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
    
    On Error Resume Next
    Set wks = ActiveSheet
    x = 2
    Do Until wks.Range("A" & x) = ""
        str = wks.Range("A" & x)
        Worksheets(str).Name = wks.Range("B" & x)
        x = x + 1
    Loop
End Sub

The extra line of code restricts the macro from executing if the change is not in Column B.

NB this MUST reside on the code page of the sheet containing the names.

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.
Abhijeet    22 Jun 2015, 04:39
@ Berry
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.
i am using the same macro, pl see:
Sub RenameSheets()
    Dim x As Integer
    Dim wks As Worksheet
    Dim str As String
    
    On Error Resume Next
    Set wks = ActiveSheet
    x = 2
    Do Until wks.Range("A" & x) = ""
        str = wks.Range("A" & x)
        Worksheets(str).Name = wks.Range("B" & x)
        x = x + 1
    Loop
End Sub
Barry    17 Jun 2015, 05:12
@Abhijeet

I'm glad it now works the way you want it.

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.
Try:
excel.tips.net/T007425_Combining_Worksheets_from_Many_Workbooks.html
excel.tips.net/T003005_Condensing_Multiple_Worksheets_Into_One.html
excel.tips.net/T002409_Merging_Many_Workbooks.html
excel.tips.net/C0180_Worksheets.html
Abhijeet    16 Jun 2015, 05:12
@
Berry
Hi, thanks for your help, it works file.
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?
Barry    15 Jun 2015, 06:17
@Abhijeet

Sorry the attempt at the list lost most of its formatting - please view the screen shot on this link: http://tinyurl.com/on4pd6v
Barry    15 Jun 2015, 06:07
@Abhijeet

The following code will rename specified sheets from a list of existing sheet names and new sheet names.

First create a list on an un-used/new worksheet like this:

       A B C D E F
1 Existing Name New Name
2 Sheet1 NewName1
3 Sheet2 NewName2
4 Sheet3 NewName3

Note: the first row is just a header and will be ignored by the macro

Put the following macro on the same CodePage as the above list (right-click on the sheet tab and select "View Code").

Sub RenameSheets()
    Dim x As Integer
    Dim wks As Worksheet
    Dim str As String
    
    On Error Resume Next
    Set wks = ActiveSheet
    x = 2
    Do Until wks.Range("A" & x) = ""
        str = wks.Range("A" & x)
        Worksheets(str).Name = wks.Range("B" & x)
        x = x + 1
    Loop
End Sub

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.
Abhijeet    15 Jun 2015, 04:01
@ BERRY
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.
Barry    13 Jun 2015, 09:50
@Abhijeet

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.

Note: before using the second macro you must select the 12 sheets to be renamed.

Sub RenameSheets()
    'renames sheets beginning with "Sheet"...
    Dim sht As Worksheet
    Dim x As Integer
    
    On Error Resume Next
    x = 0
    For Each sht In Worksheets
        If Left(sht.Name, 5) = "Sheet" Then
             x = x + 1
             If Range("A" & x) = "" Or x = 13 Then Exit For
             sht.Name = Range("A" & x)
        End If
    Next sht
End Sub

Sub RenameSheetsII()
    'renames selected sheets
    Dim sht As Worksheet
    Dim x As Integer
    
    On Error Resume Next
    x = 0
    For Each sht In ActiveWindow.SelectedSheets
       x = x + 1
       If Range("A" & x) = "" Or x = 13 Then Exit For
       sht.Name = Range("A" & x)
    Next sht
End Sub
Abhijeet    13 Jun 2015, 05:25
Hi Berry,
can you pls give me a sample macro for the same.
JB    12 Jun 2015, 08:45
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.
====================
Sub RenTab()
'
' RenTab Macro
' Renams a worksheet to the contents of A1, up to 31 characters (Excel's imposed limit)
' Keyboard Shortcut: Ctrl+Shift+P
'
    ActiveSheet.Name = Left(ActiveSheet.Range("A1"), 31)
End Sub
====================
Barry    12 Jun 2015, 08:43
@Abhijeet

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.
Abhijeet    12 Jun 2015, 05:55
@Berry
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.
Barry    11 Jun 2015, 09:53
@Abhijeet

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.

E.g. is it the leftmost 12 worksheets, or
rightmost 12 worksheets, or
sheets with existing name's Sheet1, Sheet2, ...Sheet 12; or
random choice, or
some other criteria.
Barry    11 Jun 2015, 09:47
@JB

The line of code:

ActiveSheet.Name = Left(Target, 31)

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.

NB this doesn't check for illegal characters which would result in the erro handler being invokded.
Abhijeet    11 Jun 2015, 08:09
Hi,
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?
JB    10 Jun 2015, 09:56
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.

What I'd like to do is have the macro use the first 31 characters if A1 is larger than that.

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.

Can this be accomplished easily?

I need this to be a macro that I can assign to a keystroke.
John    05 May 2015, 13:58
I am scouring the earth to find a solution:

I have a Workbook with a Summary Tab.

Summary Tab Contains:
EmplID
Name (80)
Region (9 Regions)
Total Sales

Then a tab for each Sales Person (The tab name reads Smith, L John. There are 80 sales people) the tab includes:
EmplID
Name
OrderID
Sales

Every month I have to split up files into 9 new workbooks, 1 for each region manager including only their sales people.

My idea was to tag the region in each sheet (INDEX MATCH on Summary to bring in Region)

Use the article macro to change tab name to region and then I am stuck... Any help would be really appreciated.



Sandy    01 May 2015, 04:43
Hi I'm looking to use the macro cell A1 date to change the tab name to that date.
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
thanks
sherri    23 Apr 2015, 18:49
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
Veer    25 Mar 2015, 02:58
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.
Ralph    10 Feb 2015, 16:53
Thanks Awyatt!
BW    30 Jan 2015, 10:59
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.

I appreciate any assistance.

Thank you!
BW
awyatt    07 Jan 2015, 17:15
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.

-Allen
marta    07 Jan 2015, 16:27
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.
How could I fix this error without erasing characters/letters
Cathy McCullough    22 Dec 2014, 20:06
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.

Thanks so much
Catherine
Barry    25 Sep 2014, 12:15
@ Lance

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.

Firstly input the tab name into a variable e.g.:

Dim NewTabName as String

NewTabName = InputBox("What name do you want to give the Tab?")

then modify this line of code from:

"=IF('100.RTR'!R[7]C[105]=""ISSUED FOR 30% REVIEW"",30%,"""")"

to:
 ActiveCell.FormulaR1C1 = "=IF('" & NewTabName & "'!R[7]C[105]=""ISSUED FOR 30% REVIEW"",30%,"""")"

and repat the same substitution on the other line that reference the sheet "100.RTR".

This doesn't include any error handling code for invalid Tab names though.
Lance    24 Sep 2014, 09:54
I've been looking for the solution if there is a solution.

firstly here is my marco:
Sub Macro3()
'
' Macro3 Macro
' 3rd Test
'
' Keyboard Shortcut: Ctrl+e
'
' varCellContent = Application.InputBox _
        (prompt:="Choose a sheet by clicking on any cell in it.", Type:=8)

  ' strDestinationSheetName = ActiveSheet.Name
  'Dim myValue As Variant
  ''myValue = InputBox("Give me some input")
      
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "=MID(RC[3],1,1)"
    
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=MID(RC[2],2,1)"
    
    Range("C2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF('100.RTR'!R[7]C[105]=""ISSUED FOR 30% REVIEW"",30%,"""")"
        
    Range("D2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[8]='100.RTR'!R[7]C[14],IF(LEN('100.RTR'!R[7]C[-2])=9,'100.RTR'!R[7]C[-2],""""),"""")"
        
    Range("E2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[7]='100.RTR'!R[7]C[13],'100.RTR'!R[7]C[7],"""")"
    
    Range("F2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[6]='100.RTR'!R[7]C[12],CONCATENATE('100.RTR'!R[7]C[9]),"""")"
        
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "=MID(RC[2],1,3)"
    
    Range("L2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF('100.RTR'!R[7]C[96]=""ISSUED FOR 30% REVIEW"",CONCATENATE('100.RTR'!R[7]C[6]),"""")"
        
    Range("N2").Select
    ActiveCell.FormulaR1C1 = "=MID(RC[-10],4,3)"
    
    Range("Q2").Select
    ActiveCell.FormulaR1C1 = _
        "=REPLACE(IF(LEN(RC[-13])=9,CONCATENATE(RC[-13],RC[-12]),""""),3,1,"""")"
        
    Range("R2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-8]=""DSC"",""DOC"",IF(RC[-8]="""","""", IF(RC[-8]=""DOC"",""DOC"",""DWG"")))"
           
    Range("A2:R2").Select
    Selection.AutoFill Destination:=Range("A2:R1000"), Type:=xlFillDefault
   
 With Range("A2:A1000")
    .Range("A2:R1000").Copy
    .Range("A2:R1000").PasteSpecial Paste:=xlPasteValues
    .Value = .Value
    .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    
End With

End Sub


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 hope you guys can help me.

thank you very much.
Stephanie    19 Aug 2014, 05:44
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?
Barry    01 Jul 2014, 08:50
@MarlonSC

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).

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Static oldRng As Variant
    Dim rng As Range
    Dim i As Integer
    
    Set rng = Range("A1")
    If rng.Value = oldRng Then Exit Sub
    oldRng = rng.Value
    If rng.Value = "" Then Exit Sub
    If IsDate(rng.Value) <> True Then Exit Sub
    If Worksheets.Count < 7 Then Exit Sub
    On Error GoTo Badname
    For i = 1 To 7
        Worksheets(i).Name = Format(rng.Value + i - 1, "mm_dd_yy")
    Next i
    Exit Sub

Badname:
    MsgBox "Please revise the entry in A1." _
    & Chr(13) _
    & "It appears to contain one or more " & Chr(13) _
    & "illegal characters." & Chr(13)
    Range("A1").Activate
End Sub

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.
MarlonSC    30 Jun 2014, 14:08
@ Barry,

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.
PatL    13 Jun 2014, 16:48
Thanks Barry. I'll give it a try.
Barry    11 Jun 2014, 07:56
@Pat L

Try this (change A1 to either D1 or F1 as required):

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Dim TabName As String
    
    If IsDate(Range("A1")) = True Then
        TabName = Format(Range("A1"), "mm_dd_yy")
    Else
        TabName = Range("A1")
    End If
    If TabName = "" Then Exit Sub
    On Error GoTo Badname
    ActiveSheet.Name = Left(TabName, 31)
    Exit Sub
Badname:
    MsgBox "Please revise the entry in A1." _
    & Chr(13) _
    & "It appears to contain one or more " & Chr(13) _
    & "illegal characters." & Chr(13)
    Range("A1").Activate
End Sub

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.
Pat L    10 Jun 2014, 11:12
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 _ ?
Thanks
Pat
PhilP    30 May 2014, 12:49
@Cathyp

Try this one line of code to be added to your existing sheet creation macro - probably immediately after the bit that creates each sheet.
 
Range("A1") = Sheets(1).Name

Change the A1 to cell for sheet name and (1) to be the number of the added sheet.

Feel free to email me if required
Ste    28 May 2014, 10:50
Try opening VB then selecting "Insert" then "Module" and put the code into there so it applies to all sheets
cathyp    24 Apr 2014, 06:50
Hi

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.
Mohammed    21 Apr 2014, 06:16
Dear Allen

Thanks for the beutiful and very useful tips.

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.

Appreciate if you can help me in this.

Note: I'm not familure with macros.

Thanks
Anouk    16 Jan 2014, 05:09
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?
Thanks in advance!
Dana    05 Jan 2014, 11:32
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.
Dana    05 Jan 2014, 11:26
Works for plain text but not when the cell contents are from a formula [such as NOW()].
Kamal    04 Jan 2014, 13:22
I tried to do as per the instruction but the sheet is not getting renamed as per A1.... Kindly elaborate.
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.