Create, Rename Worksheets, Conditional IF Macro

[twnty3svn]

Hi everyone,

I'm new to the forum, and I have searched far and wide to find a macro to accomplish the below. Here is my current worksheet.

- Single sheet named "Staff" for which cells A2:A34 contain staff names and cells B2:B34 contain staff "titles" (4 different titles "Operations", "Register", "Manager" & "Sales".

- 4 Separate worksheet templates for each title named "Cook Master", "Register Master", "Manager Master" & "Sales Master"

I"m trying to create a macro that will:

1. Create a copy of the corresponding master sheet for each staff member in column A based on their title in column B;
2. Rename cell A1 in each sheet with the staff members name from the list;
3. Loop that if the macro button is pressed a subsequent time, for any new/changed names on the list new worksheets are created, and duplicates are ignored.

I am using the following code to create and rename, however it is based on one master sheet.

Sub CreateStaff()
    Sheets("Staff").Activate
    For Each cell In Sheets("Staff").Range("A2", Range("A34").End(xlUp))
         
        If Not SheetExists(cell.Value) Then
            Sheets("Master").Copy After:=Sheets(Sheets.Count)
            With ActiveSheet
                .Name = cell
                .Range("A1") = cell
            End With
        End If

        Sheets("Staff").Activate
    Next

End Sub

Any help would be much appreciated.

EDIT: I'm using Windows Excel 2010 and Mac Excel 2011

 

[jbeaucaire]

Try this:

Sub CreateStaff()
Dim cell As Range

With Sheets("Staff")
    For Each cell In .Range("A2:A34").SpecialCells(xlConstants)
        If Not SheetExists(cell.Value) Then
            Sheets(cell.Offset(, 1) & " Master").Copy After:=Sheets(Sheets.Count)
            With ActiveSheet
                .Name = cell.Value
                .Range("A1").Value = cell.Value
            End With
        End If

        .Activate
    Next cell
End With

End Sub

 

[twnty3svn]

Thank you so very much jbeaucaire! The Macro works beautifully

One small refinement if possible, is there a way to sort the worksheets by Title (in a set order ie. Manager, Register, Sales,Cook) and then by Staff in alphabetical order?

ie. Instead of worksheet order (if this was the table order):

Sue Cook
Vacant Register
Kerry Register
Amy Cook
Jerry Cook
Sam Manager
Tim Register
Tony Sales

We get This:

Sam Manager
Kerry Register
Vacant Register
Tony Sales
Amy Cook
Jerry Cook
Sue Cook

 

[jbeaucaire]

But the sheetnames themselves don't actually say "manager" and "register" in them, right? In the resulting sheets is there a cell somewhere on the sheet that does say that?

 

[twnty3svn]

Yes you are right, the sheets do not say "manager" and "register", in Cell A2 I have a vlookup to determine the title based on the name of the staff member.

 

[twnty3svn]

On other thing I've noticed which is a small issue is if I hide the master sheets, then it does not create visible copies of these sheets; any way to overcome this?

Also I am trying to get these sheets to be created between two worksheets named "First" and "Last"; any ideas how?

Thanks

 

[jbeaucaire]

Ok, this will use a second macro to find the LAST sheet that currently has "manager" in cell A2, then add the new sheet after that. If the new sheet is a "cook" then it will find the last sheet with "cook" in A2 and add the new sheet after that.

If this is the FIRST "manager" sheet being created, then it will insert this sheet after your sheet named "First".

This macro also will unhide the sheets before making copies, then hide the template again.

Option Explicit

Sub CreateStaff()
Dim cell As Range, MyIndex As Long

With Sheets("Staff")
    For Each cell In .Range("A2:A34").SpecialCells(xlConstants)
        If Not SheetExists(cell.Value) Then
            With Sheets(cell.Offset(, 1) & " Master")
                .Visible = True
                MyIndex = FindIndex(cell.Offset(, 1).Value)
                .Copy Before:=Sheets(MyIndex)
                .Visible = False
            End With
            With ActiveSheet
                .Name = cell.Value
                .Range("A1").Value = cell.Value
            End With
        End If

        .Activate
    Next cell
End With

End Sub

Function FindIndex(MyStr As String) As Long
Dim ws As Worksheet, Found As Boolean

    For Each ws In Worksheets
        If ws.[A2] = MyStr Then
            Found = True
        Else
            If Found = True Then
                FindIndex = ws.Index
                Exit Function
            End If
        End If
    Next ws

    'Add after sheet named "FIRST" if this is the first sheet of its kind
    FindIndex = Sheets("First").Index + 1
End Function

 

[twnty3svn]

jbeaucaire thank you very much, your help with this is much appreciated.

The worksheets work fine now while the master sheets are hidden. Is there any way to define the order of the created sheets if the job titles change in the future?

Finally I'm still having issues creating the sheets between worksheets "First" and "Last". I forgot to mention that these two sheets are hidden, and I'm presuming that it is the reason its not working. The two hidden sheets are always before the created sheets.

The reason why these sheets are hidden is because the summary page uses a SUM formula =SUM(First:Last!G15) to add up the created pages, and the "First" and "Last" sheets are only for reference. If we could get this figured out, it would be perfect!

Thanks

 

[jbeaucaire]

The macro retains the order based on the currently listed sheets. If a new category is introduced and you do not manually set the first real visible sheet in place, then the macro will place this new sheet after your hidden sheet FIRST.

As long as the existing visible sheets are between FIRST:LAST, then new ones added to the same categories would be placed with the other sheets of the same category, so it should also end up between FIRST:LAST.

 

[twnty3svn]

Thanks for all your help. Managed to finally finish the spreadsheet.