View Full Version : Macro to create new sheets when master is updated

becky halsey
01-18-2017, 01:21 PM
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!

01-20-2017, 10:36 PM
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
Application.DisplayAlerts = True
MsgBox "You can not use """ & shn & """ for Worksheets name!"
End If
On Error GoTo 0
MsgBox "Worksheets """ & shn & """ already exists!"
End If
End If


End Sub

becky halsey
01-23-2017, 08:38 AM
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!

01-25-2017, 04:40 AM

becky halsey
01-25-2017, 10:17 AM
Thanks so much!