Automatically create a copy of Master sheet when data entered into specific rows

Thanks for that, dflak! I love that it can make a new sheet with the name I put in! That works perfect for me and is exactly what I was thinking. I'm super new to Excel and this thing amazes me more and more. Haha!

I started playing around with it a bit and noticed a few things:

1) when I changed the "Holiday Week Variables" (Staff!AT9) from 7 to 5, it created a new sheet named "5".
2) when I input dates in the "Staff Holidays Approved" (Staff!B4:M80) the code doesn't like that I use a "/" and gives an error.

Is there any way to amend the code so that it only looks for changes to Staff!A4:A80? And can it only create a new sheet when the cell goes from blank to having a name in it?

3) When I erased all the names in the A column then tried it out to add a name to it (in cell A5), all of a sudden it has hidden rows A7:A80. This is a problem because it then hides the calendar which I would not like hidden. Is it possible to have it hide only rows A22:A80?

The idea of the sheet is to be able to see, at a glance, when too many people are away on holidays based upon the variables on the right side of Staff!. The calendar changes when the staff holidays approved start/end = the variable set on the right side.

The people using this document are very, very uneducated in excel so I am trying to make it as simple for them as possible. Hence the auto add and name new sheet, the hiding rows until they are ready to put a name in it.

So far, with the help of this amazing community, everything has worked perfectly and now the last bit is to have the auto add and name new sheet going.

Thanks so much for your help!

Found the problem and fixed it. Now it works as desired... Thank you!

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lastRow As Long ' Last row this sheet
Dim sh As Worksheet ' This worksheet

If Target.Cells.Count > 1 Then Exit Sub

' Find the last row of data
lastRow = Cells(Rows.Count, "A").End(xlUp).Row

If Target.Column = 1 And Target.Address <> "$A$" & lastRow + 1 And Target.Value <> "" Then

Application.ScreenUpdating = False

If SheetExists(Target.Value) = True Then
MsgBox "Sheet " & Target.Value & " already exists." & Chr(10) & "Exiting program.", _
vbOKOnly + vbCritical, "Sheet Exists"
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
Exit Sub
End If

Set sh = ActiveSheet

' copy the Master Do Not Delete sheet
Sheets("Master Do Not Delete").Copy after:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = Target.Value
Sheets(Sheets.Count).Visible = True

' Cosmetics
sh.Select
Cells(lastRow + 1, "A").Select

' Hide rows
Range(Cells(1, "A"), Cells(80, "A")).EntireRow.Hidden = False
If lastRow < 80 Then
Range(Cells(Application.WorksheetFunction.Max(23, lastRow + 2), "A"), Cells(80, "A")).EntireRow.Hidden = True
End If

Application.ScreenUpdating = True

End If
End Sub

Public Function SheetExists(SheetName As String) As Boolean
Dim sh As Worksheet

For Each sh In ActiveWorkbook.Sheets
If sh.Name = SheetName Then
SheetExists = True
Exit Function
End If
Next sh
SheetExists = False
End Function