I am using the code below (Excel 2007) to delete and add back timesheets based on a list of names from the “Names” tab. In addition, I am making a “Table of Contents” with hyperlinks on a separate worksheet that will allow employees quick access to their timesheet without having to look at each tab. The code below works well if I want to delete and add back all the timesheet at one time but if we get a new employee in the middle of a pay period I have to use a single timesheet until the end of the pay period.

What I would like to know is, can the code below be modified so that when the code is run it reviews the list of names and only adds a new timesheet for that employee without deleting and adding back all the timesheets?



Sub CreateSheetsFromAList1()
Dim ws1 As Worksheet
Dim MyCell As Range, myRange As Range
'Only to be used to create tabs based on Names
Application.DisplayAlerts = False

For Each ws1 In ThisWorkbook.Worksheets
    If ws1.Name <> "Master" And ws1.Name <> "Table of Contents" And ws1.Name <> "Names" And ws1.Name <> "Lookup" Then ws1.Delete
Next ws1
    
    Set ws1 = ThisWorkbook.Worksheets("Master")
Worksheets("Names").Visible = xlSheetVisible
Worksheets("Master").Visible = xlSheetVisible

Sheets("Master").Select
'Call Macro5
    
    Set myRange = Sheets("Names").Range("B2")
    Set myRange = Range(myRange, myRange.End(xlDown))
On Error GoTo ErrHandler
    For Each MyCell In myRange
       ws1.Copy ThisWorkbook.Sheets(Sheets.Count)
       ThisWorkbook.Worksheets("Master (2)").Name = MyCell.Value
    
    
    Next MyCell


ErrHandler: Call DelSht
Call CreateTableOfContents
Application.DisplayAlerts = True
Worksheets("Names").Visible = xlSheetHidden
Worksheets("Master").Visible = xlSheetHidden
Sheets("Table of Contents").Range("A2").Select
End Sub