VBA - Create New Worksheet from list

[MrKen]

VBA - Create New Worksheet from list

I need some excel 2012 VBA help please.
Table2 in the worksheet named "Index" has employee information.
The code below reads the list and creates a new worksheet and renames it based on contents in column e and increments to the next This code works for new set up.

The next step I need help with is this. I want to add additonal employees. Could be 1 or 100 at a time. I need to either mod this code or create a new sub, either is fine with me.

1 - I would like for the code to find the new entries and create new worksheets based on the new entry only and thus skipping already created worksheets. There is very low risk of getting a worksheet with the same name as all cell values in column e are unique. I know worksheets have a 31 charater limit, but I am not worried it will be an issue for this project.
2 - The new worksheets will be created based also on a template named "template" is was done in the below code.
3 - Table looks like this :</SPAN>
</SPAN>
Thanks.</SPAN>
MrKen
---------------
[TABLE="width: 415"]
<TBODY>[TR]
[TD]Employee ID</SPAN>
[/TD]
[TD]First </SPAN>
[/TD]
[TD]2nd </SPAN>
[/TD]
[TD]Last </SPAN>
[/TD]
[TD]Worksheet Name</SPAN>
[/TD]
[/TR]
[TR]
[TD]B87687</SPAN>
[/TD]
[TD]Ronny</SPAN>
[/TD]
[TD]Karl</SPAN>
[/TD]
[TD]Fernández</SPAN>
[/TD]
[TD]FernándezB87687</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

 Sub CreateSheetsFromList()
Application.ScreenUpdating = False
Dim employeeCell As Range, employeeIDcol As Range

Set employeeIDcol = Sheets("index").Range("e2")
Set employeeIDcol = Range(employeeIDcol, employeeIDcol.End(xlDown))

For Each employeeCell In employeeIDcol
Sheets("template").Select ' Select the templete as the new worksheet to create
Sheets("template").Copy After:=Sheets(Sheets.Count) 'Creates a new worksheet based on the template. Worksheet will be named templete(1), (2) ect....
Sheets(Sheets.Count).Name = employeeCell.Value ' Renames the worksheets from the index of employees.
Range("R2").Select ' Select cell with employee ID number
Selection.Copy 'Copy cell with employee ID number
Range("J3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False ' Paste as a value the employee ID number in J3
Application.CutCopyMode = False ' Deselect the copy
Range("A1").Select

Next employeeCell
Application.ScreenUpdating = True
End Sub

</SPAN>

 

[dmt32]

Try testing for worksheet exists at top of your For Loop.

something like this:

For Each employeeCell In employeeIDcol
        On Error Resume Next
        If IsError(Worksheets(employeeCell)) Then
            ' if sheet does not exist you get an error
            'run your routine
        End If
    Next employeeCell

Dave

 

[MrKen]

Thank you, I added the error code. It works when run the first time, but if i add a new employee record and run the code again, it creates new worksheets named temple(1), (2) ect.... then at the end it creates the new employee worksheet. So If i have 100 employees already in the list, I will get 100 new blank worksheets.

This is what im trying to fix. I would like it find only new records, skip old ones and create new worksheet based on the newly added employee.

Try testing for worksheet exists at top of your For Loop.

something like this:

For Each employeeCell In employeeIDcol
        On Error Resume Next
        If IsError(Worksheets(employeeCell)) Then
            ' if sheet does not exist you get an error
            'run your routine
        End If
    Next employeeCell

Dave

 

[MrKen]

Is there a way to loop from the bottom of the list and stop when it finds the first duplicate? or error.

 

[dmt32]

sorry omitted .value in test line

If IsError(Worksheets(employeeCell.Value)) Then

 

[MrKen]

sorry omitted .value in test line

If IsError(Worksheets(employeeCell.Value)) Then

That is excellent. Works perfect. Thank you.

In case anyone else wants to use the code here it is

Sub CreateSheetsFromList()
    Application.ScreenUpdating = False
    Dim employeeCell As Range, employeeIDcol As Range
    'Dim ws As Worksheet
    
    Set employeeIDcol = Sheets("index").Range("e2")
    Set employeeIDcol = Range(employeeIDcol, employeeIDcol.End(xlDown))
    
    For Each employeeCell In employeeIDcol
      On Error Resume Next
        If IsError(Worksheets(employeeCell.Value)) Then
    Sheets("template").Select ' Select the templete as the new worksheet to create
    Sheets("template").Copy After:=Sheets(Sheets.Count) 'Creates a new worksheet based on the template. Worksheet will be named templete(1), (2) ect....
    Sheets(Sheets.Count).Name = employeeCell.Value ' Renames the worksheets from the index of emplohyees.
    Range("R2").Select ' Select cell with employee ID number
    Selection.Copy  'Copy cell with employee ID number
    Range("J3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False ' Paste as a value the employee ID number in J3
    Application.CutCopyMode = False ' Deselect the copy
    Range("A1").Select
    End If
    
Next employeeCell
Application.ScreenUpdating = True
Sheets("Index").Select
End Sub

 

[Fodog]

Hello MrKen...
Can you upload a sample of the actual workbook with sample dummy data? I'm getting back into VBA and this scenario is exactly what I'm looking for. But my brain needs a kick-start.
Thank you...
Fosdog