Inserting worksheets automatically in the same workbook..Guidance reqd

[shrejeet]

Hello Everybody

I have a named range (cname) which contains names like
abc
def
ghi
jkl
mno
pqr
stu...
.... and more..
I want to automatically create worksheets with the formulas and format as in the first sheet, in the same workbook with the above names like sheet1 will be named as abc, sheet2 as def, sheet3 as ghi... so on

If i add any name, a new sheet should be inserted in the workbook while checking for duplication. No duplicate sheet for the same name should be inserted. The sheet name could be more than 3 characters

Can anybody tell me how to go about this..
any macros available?

Thanks a ton in advance
Rajesh

[Bob Phillips]

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<== change to suit
Const SHEET_2COPY As String = "Master" '<== change to suit
Dim sh As Worksheet

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
On Error Resume Next
Set sh = Worksheets(.Value)
On Error GoTo 0
If sh Is Nothing Then
Worksheets(SHEET_2COPY).Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = .Value
End If
On Error GoTo 0
Me.Activate
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
[/vba]

This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

[shrejeet]

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<== change to suit
Const SHEET_2COPY As String = "Master" '<== change to suit
Dim sh As Worksheet

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
On Error Resume Next
Set sh = Worksheets(.Value)
On Error GoTo 0
If sh Is Nothing Then
Worksheets(SHEET_2COPY).Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = .Value
End If
On Error GoTo 0
Me.Activate
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
[/vba]

This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

Thanks for the input
But this doesnt seem to work
there is no change taking place
Pls help...

Thanks
Rajesh

[Bob Phillips]

You need a template sheet called Master, and to type something into one of H1:H10.

[shrejeet]

You need a template sheet called Master, and to type something into one of H1:H10.

Hi
I have a template called Master
I tried the same but a new sheet Master(2) is only being created

It is not being named as i want and only one sheet is being created

Thanks
Rajesh

[Bob Phillips]

Your example workbook works fine for me. Just re-edit the names to get them retrospectively.

[shrejeet]

Your example workbook works fine for me. Just re-edit the names to get them retrospectively.

Unfortunately it doesnt seem to work here.. I am using excel 2007
Do i have to run the code every time ?
Help will be highly appreciated
Can the file be attached??
Rajesh

[Bob Phillips]

Works fine for me in 2007 also.