@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
Loading
ExcelTips (Menu Interface)
This Site
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
Author Bio
With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company.
Learn more about Allen...
Subscribe
Helpful Links
ExcelTips FAQ
ExcelTips Resources
Ask an Excel Question
Make a Comment
Free Business Forms
Free Calendars
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:
- Open a new workbook that has only one worksheet in it.
- Right-click the worksheet tab and select View Code from the resulting Context menu. Excel displays the VBA Editor.
- Paste (or type) the above macro into the code window.
- Close the VBA Editor.
- Locate the XLStart folder on your system. (Use the Windows search capabilities to locate the folder.)
- 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.
- 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:
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
Leave your own comment:
Comments for this tip:
Barry 07 Sep 2016, 05:17
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.
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
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.
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
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.
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!
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.
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
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.
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.
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
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
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.
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
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
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
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.
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
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
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")
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.
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")
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")
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!
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
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!!
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
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
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.
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.
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.
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!!!
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.
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?
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.
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.
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!!
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!!
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.
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.
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
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
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?
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
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.
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.
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
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.
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
====================
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.
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.
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.
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.
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?
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.
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.
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
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.
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
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
-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
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
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.
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.
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.
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.
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.
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
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
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.
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
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!
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.