Solved how to make sheet names populate from cells

April 29, 2010 at 15:54:30
Specs: Windows XP

Hello, I was wondering if there is a way to make the sheet names of a workbook automatically fill with the contents of a list that is on the first sheet. In other words, on the first sheet I have a list of names, like John Smith, Mary Jones, etc., and then I want sheet two to be called John Smith, sheet three to be called Mary Jones, etc.
Thanks in advance for any help...

See More: how to make sheet names populate from cells

Report •


#1
April 30, 2010 at 04:29:27

Hi,

The following macro will make a series of worksheets from a list of names in column A.

When run again, the macro will just create worksheets for any new names.

Note that the macro does not test for valid worksheet names - a non-valid name will just cause the macro to error out (although the macro does ignore empty cells in the list of names).

If the list of names is not in column A or does not start in row 1, change the values in these lines:

''setup column letter and first row number containing names
'column
strCol = "A"
'row (number is in double quotes)
strRow = "1"

On the Worksheet containing the names, create a command button from the Control Toolbox toolbar.
(If this isn't visible, right click on an existing toolbar and check the Control Toolbox).
Select the button Icon and draw a button
Right-click the button and select Command Button - Edit and change the name to 'Add Sheets' or something else suitable.

Right-click the button again and select View Code
In the code window that opens enter this:

Option Explicit

Private Sub CommandButton1_Click()
Dim strCol As String
Dim strRow As String
Dim rngStart As Range
Dim rngEnd As Range
Dim rngCell As Range
Dim strWsName As String
Dim strSrcName As String

On Error GoTo ErrHnd

'setup column letter and first row number containing names
'column
strCol = "A"
'row (number is in double quotes)
strRow = "1"

'turn off screen updating to stop flicker & increase speed
Application.ScreenUpdating = False
            
'save this worksheet's name, so we can go back to it later
strSrcName = ActiveSheet.Name

'set start of data in selected column
Set rngStart = ActiveSheet.Range(strCol & strRow)
'find end of data in selected column
Set rngEnd = ActiveSheet.Range(strCol & CStr(Application.Rows.Count)) _
            .End(xlUp)

'loop through cells in used range
For Each rngCell In ActiveSheet.Range(rngStart, rngEnd)
    'ignore empty cells in range
    If rngCell.Text <> "" Then
        'get worksheet name
        strWsName = rngCell.Text
        'test if worksheet exists
        On Error Resume Next
        If Worksheets(strWsName) Is Nothing Then
            'worksheet does not exist
            'reinstate error handling
            On Error GoTo ErrHnd
            'create new sheet
            Worksheets.Add After:=Worksheets(Worksheets.Count)
            'name new sheet
            Worksheets(Worksheets.Count).Name = strWsName
            Else
            'worksheet already exists
            'reinstate error handling
            On Error GoTo ErrHnd
        End If
    End If
Next rngCell

'go back to the source worksheet
Worksheets(strSrcName).Activate

'reinstate screen updating
Application.ScreenUpdating = True
Exit Sub

'error handler
ErrHnd:
Err.Clear
'go back to the source worksheet
Worksheets(strSrcName).Activate
'reinstate screen updating
Application.ScreenUpdating = True
End Sub

Note that Private Sub CommandButton1_Click() and End sub will already be present, so don't duplicate them. Option Explicit goes before Private Sub CommandButton1_Click().
Some lines of code have been split onto two lines for ease of viewing, using the line continuation character "_". This should work 'as is' just copy and paste, or you could remove the "_" and bring the code back to one line.

Click Save from the Visual Basic Menu.

Alt+f11 takes you back to the main Excel window.
Exit design mode (first icon on the Controls Toolbox toolbar).

As changes made by Macros cannot be undone with the Undo button, test this macro on a copy of your data. Always make a backup of your Workbook before running this macro. This code has only been tested on sample data, and it has not been tested in your environment, so test it on copies of your data to ensure that it works 'as expected'

Click the 'Add Sheets' button to run the macro

Regards


Report •

#2
April 30, 2010 at 11:29:20

Thanks for the reply - I have never made a macro and had no idea what I was doing, but it did work. Very fascinating! I did not realize though, that it would create all new sheets - I wanted it to name the sheets I already had created in that workbook, because they are already formatted the way I want them. Is this possible?

Report •

#3
May 1, 2010 at 05:47:17
✔ Best Answer

Hi,

You can use it to rename sheets, but how will the macro know which sheets to rename.

Another option is to have one empty (template) worksheet formatted as you need it, then each new worksheet is a copy of that template, complete with name.

To create the new worksheets using a worksheet named "Template", just replace the code that creates the sheets with this:

        If Worksheets(strWsName) Is Nothing Then
            'worksheet does not exist
            'reinstate error handling
            On Error GoTo ErrHnd
            'copy worksheet named "Template"
            Worksheets("Template").Copy After:=Worksheets(Worksheets.Count)
            'name new sheet
            Worksheets(Worksheets.Count).Name = strWsName
            Else
            'worksheet already exists
            'reinstate error handling
            On Error GoTo ErrHnd
        End If

Let me know what you think.

Regards


Report •

Related Solutions

#4
May 1, 2010 at 08:14:04

If the sheets are in the same order as the names on the list, then renaming them is fairly simple.

Assuming the list of names is in Sheet1!A1:An, and assuming you have n + 1 sheets in your workbook (one for each name, starting with Sheet 2, plus Sheet 1 with the list), this code will rename each sheet, in order, based on the list in Sheet1!A1:An.

Sub RenameSheets()
Dim nxtName As Integer
 For nxtName = 1 To Sheets.Count - 1
  Sheets(nxtName + 1).Name = Sheets(1).Cells(nxtName, 1)
 Next
End Sub

If the sheets are not in the same order as the list, then things get a bit more complicated. If each name appeared someplace in that person's sheet, then the code could search each sheet for the name and then rename that specific sheet to match the name it was searching for.

Before I offer any code to accomplish that, I'd need to know a little bit about your workbook, such as whether the names appear in the same cell (or a specific) in each sheet; does only that person's name appear in the sheet; etc.


Report •

#5
May 3, 2010 at 11:28:41

Hello,

Humar, your response was very helpful once again, thanks. I tried it and it worked. DerbyDad03, I think your suggestion will be the one best suited to the spreadsheet I want to create; but not really knowing anything about macros I am unsure where those lines are supposed to go. My list of names is in Sheet1!A12:A31, I also wasn't sure how to change the code to fit those numbers.
If it helps, what I'm trying to create is this: I work for a university and many of the people in my office travel frequently to conferences, etc. To keep track of their expense reimbursements, I want a template where we could input all the conference info on the first page, with a list of all the attendees, then use another template which would automatically populate with the conference information from the first page, as well as have spaces to break down all the traveler's specific information and expenses, and this second template would duplicate itself once for each traveler, naming itself with the traveler's name. Hopefully this makes sense.
So I think that DerbyDad03's first suggestion is what I want, I'm just not sure how to work it into the macro.


Report •

#6
May 3, 2010 at 12:21:54

I think this does what you want as far as renaming the sheets.

Allow me explain how the original code works, so you can see how the change I made impacts it's operation.

For nxtName = 1 To Sheets.Count - 1

This is a For-Next loop that counts from 1 to "the number of sheets in your workbook minus 1". i.e. With 10 sheets, it will count from 1 to 9.

Each time through the loop, it sets the variable nxtName equal to the current value (1 through 9)

Sheets(nxtName + 1).Name = Sheets(1).Cells(nxtName, 1)

Sheets(nxtName + 1).Name refers to the name of the sheet tab for each sheet. The first time through the loop, nxtname = 1 so the first time through the loop the it refers to Sheets(2), or the second sheet in the workbook.

Sheets(1).Cells(nxtName, 1) refers to a cell on Sheets(1) whose Row Number is nxtName and whose Column number is 1. The first time through the loop, it refers to Cells(1,1) or A1, then Cells(2,1) or A2, etc.

Since your list starts in A12, all we need to do is add 11 to nxtName and it will refer to A11+1 (A12) then A12 + 1 (A13), etc.

So what we are saying is that the first time through the loop, set the name of the second sheet to be whatever is in cell A12 of Sheet1. The next time through the loop, set the third sheet's name to be whatever is in A13, etc.

Note: If you have duplicate names in your list, or special characters that aren't allowed in a sheet name or less sheets than names in the list +1, you'll get an error.

As I stated in my earlier post, it's going to name the sheets in the same order as the list, so if Sheet2 belongs to Bob Smith, but A12 contains Shirley Jones, the code won't care and it will name Sheet2 Shirley Jones.

Sub RenameSheets()
Dim nxtName As Integer
 For nxtName = 1 To Sheets.Count - 1
  Sheets(nxtName + 1).Name = Sheets(1).Cells(nxtName+11, 1)
 Next
End Sub

Now, since we know we aren't starting with "1" anymore (either Sheet1 or cell A1) we could actually do it slightly differently by counting from 2 to the total number of sheets and adjusting the cell reference by 10 so that 2 means 12, 3 means 13, etc.

In the end, they will both do the same thing.

Sub RenameSheets1()
Dim nxtName As Integer
For nxtName = 2 To Sheets.Count
Sheets(nxtName + 1).Name = Sheets(1).Cells(nxtName+10, 1)
Next
End Sub


Report •

#7
May 5, 2010 at 15:42:14

Okay, that makes sense. But where do those lines of code go, in the macro language as a whole? Do they just get tacked on at the end or do they go in the middle somewhere? Thanks for your patience,

Report •

#8
May 5, 2010 at 15:47:10

Oops, nevermind, I guess they go at the end, because I tried that and it is working now! Thanks!

Report •

#9
May 5, 2010 at 18:04:10

The new code(s) I posted are stand alone macros.

They don't get tacked on to the end of anything.


Report •

#10
May 10, 2010 at 09:01:23

What I meant was, when I go to edit the macro, and I see all the lines of code, I didn't know what I was supposed to do with the code you gave me. Obviously it must go somewhere in there for Excel to know what to do, but I didn't know if it was supposed to replace certain lines of the code I already had, or if it was supposed to be added on to what I already had. If it was to be added I had no instruction as to where I should put it, at the end or the beginning. Or if it's supposed to be a completely new macro. Not knowing anything about macros, I don't even know if you can have more than one. Sorry if this is something that seems like it should be obvious but it is not to me.

Report •

#11
May 10, 2010 at 09:57:09

Lines of code that start with Sub macro_name() and end with End Sub are complete macros, which can be run independently from other macros.

You can have countless (sort of) macros in any given workbook.

You can even have macros that call other macros.

If someone in this forum posts code that starts Sub macro_name() and ends with End Sub you can be pretty sure it's a complete macro, unto itself.

If they just post a snippet of code without those start and end lines, then it probably means it is just a section of code that needs to be added to (or replace) some existing code previously submitted.

A code snippet might also be an example of the syntax required to perform a specific operation that someone asked about. e.g.

=======

Q: "How do I find the last row with data in Column A?"

A: Try this:

Range("A" & Rows.Count).End(xlUp).Row

=======

In those cases, you can assume that instructions would be given as to what to do with the snippet.

I hope that helps.

BTW...It would behoove you to try and dissect the code so that you understand what it does. When you add macros to a workbook, you become responsible for maintaining them. Not only will you gain some coding experience so that you can do some of this on your own, but you'll be able to fix/modify the code in case we're not around.

2 tips:

1 - You can Single Step through the code, one line at a time, by placing your cursor anywhere in the code and pressing F8. This way you can watch what the code is doing which will help you understand (and learn) how the VBA language works. Trust me, I still spent a lot of time single-stepping through other people's code trying to see what they are doing.

2 - If you place your cursor inside the code and press F1, it will open the Help file for that VBA "word".

For example, take this line from the code I suggested:

For nxtName = 2 To Sheets.Count

If you place your cursor in the word Count and press F1, the Help file for the Count property will open.

However, since nxtMame is a variable set up by whoever wrote the code (me!) there is no Help file and Help will display Keyword Not Found.


Report •

#12
May 12, 2010 at 10:52:45

That is very informative, thank you!

Report •


Ask Question