Creating tabs w/in workbook - one for each name on coversheet - Macro?

[jg10009]

I'm not exactly sure how to do this but think it must be a macro, which are the final excel frontier for me so I'm out of my depth. I have a covertab in a workbook with the names of ~75 employees in, let's say, A2:A76. I need to create a separate tab for each individual in the list. This tab is the individual's timesheet and would (ideally) be renamed as the individual or as his/her employee ID. Is this possible? I need to do it for all 26 pay periods and each employee roster changes. Please please help. Thank you in advance.

 

[Tom Urtis]

It is possible but if I were you, I would use the employee ID for 3 reasons:
(1) You know that every ID will be unique, which is a requirement.
(2) You know that every ID will be no more than 31 characters in length, another requirement.
(3) You know that every ID will not contain a character that violated worksheet tab naming rules, such as a colon or slash.

Names can be used but they would need to be truncated by the code, so even if 2 peoples names are different, if the first 31 characters are the same, one of them will be disallowed.

In any case, the code will be shorter without all the compliance checks if you use an employee ID. Post back with the syntax of such an ID (so we can see if any miscreant characters are in that), and the range those IDs will be in if it is not the same as A2:A76. Also say what the tab name is or the CodeName is of the cover tab sheet.

 

[jg10009]

That makes sense! The good news is that the employee id is either 2, 3, or 4 numeric characters. Nothing fancy and no extra characters.

 

[Tom Urtis]

You did not say the name of the worksheet holding the list, so assuming you have that sheet active, and further assuming range A2:A76 holds the IDs, this macro will do what you want.

Sub Test1()
Application.ScreenUpdating = False
Dim cell As Range
For Each cell In Range("A2:A76").SpecialCells(2)
Sheets.Add(After:=Sheets(Sheets.Count)).Name = cell.Value
Next cell
Application.ScreenUpdating = True
End Sub

 

[jg10009]

You did not say the name of the worksheet holding the list, so assuming you have that sheet active, and further assuming range A2:A76 holds the IDs, this macro will do what you want.

Sub Test1()
Application.ScreenUpdating = False
Dim cell As Range
For Each cell In Range("A2:A76").SpecialCells(2)
Sheets.Add(After:=Sheets(Sheets.Count)).Name = cell.Value
Next cell
Application.ScreenUpdating = True
End Sub

This is amazing, Tom. Thank you so much. Follow-up question. Each named tab will need to have the timesheet template. What's the best way to get this onto each tab? Surely there is something other than copy/paste. My goal would be to take the template and insert the employee ID into a cell - so lifting it from the tab name and/or the master coversheet. Then that cell would be referenced in vlookups that would essentially complete the timesheet template. Does that make sense?

 

[Tom Urtis]

You would need to explain what your timesheet template exactly is. Is it a worksheet in that same workbook, or is it in a separate workbook or is it uploaded from somewhere else. Only you have seen your workbook and what you are working with, so details would be needed about where this timesheet whatever-it-is template is located and which cell the employee ID should be entered.

 

[jg10009]

You would need to explain what your timesheet template exactly is. Is it a worksheet in that same workbook, or is it in a separate workbook or is it uploaded from somewhere else. Only you have seen your workbook and what you are working with, so details would be needed about where this timesheet whatever-it-is template is located and which cell the employee ID should be entered.

Let me scrub some data and upload something for you. Please hold...

 

[jg10009]

You would need to explain what your timesheet template exactly is. Is it a worksheet in that same workbook, or is it in a separate workbook or is it uploaded from somewhere else. Only you have seen your workbook and what you are working with, so details would be needed about where this timesheet whatever-it-is template is located and which cell the employee ID should be entered.

Tom, can you see view this workbook? Payroll Test.xlsx