Macro to create new sheets when master is updated

[becky halsey]

I am looking for a code to create new sheets each time the "Master" is update & insert the "Template" in the new sheet. I also need the new sheet to be named from the master sheet. I have attached a sample file.

Thanks in advance!

[mana]

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, c As Range
Dim shn As String
Dim ws As Worksheet

On Error Resume Next
Set r = Intersect(Target, Columns(1))
On Error GoTo 0
If r Is Nothing Then Exit Sub

For Each c In r
shn = c.Value
If shn <> "" Then
Set ws = Nothing
On Error Resume Next
Set ws = Sheets(shn)
On Error GoTo 0
If ws Is Nothing Then
Sheets("TEMPLATE").Copy after:=Sheets(Sheets.Count)
On Error Resume Next
ActiveSheet.Name = shn
If Err.Number <> 0 Then
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
MsgBox "You can not use """ & shn & """ for Worksheets name!"
End If
On Error GoTo 0
Else
MsgBox "Worksheets """ & shn & """ already exists!"
End If
End If
Next

Me.Activate


End Sub

[becky halsey]

Thanks so much for the code. I entered it, but for some reason it is not running.? Have any ideas why I would be unable to run the code. I inserted it as a module. I am assuming that was correct.

Thanks again!

[mana]

http://www.ozgrid.com/VBA/run-macros-change.htm

[becky halsey]

Thanks so much!