title of new worksheet

[tintin1012000]

<table id="post2884882" class="tborder" width="100%" align="center" border="0" cellpadding="6" cellspacing="0"><tbody><tr valign="top"><td class="alt1" id="td_post_2884882" style="border-right: 1px solid rgb(255, 255, 255);">'Give the sheet a name, today's date in this example
On Error Resume Next
sh.Name = Format(Date, "yyyy-mmm-dd")
If Err.Number > 0 Then
MsgBox "Change the name of Sheet : " & sh.Name & " manually"
Err.Clear
End If
On Error GoTo 0
End Sub

I have the above code as part of a macro to create a new work sheet form a template. I want to change the naming format from ''yyyy-mmm-dd'' to yyyy-mmm-dd (A) , yyyy-mmm-dd (B) , yyyy-mmm-dd(C) and so on,

any ideas

<!-- / message --> </td> </tr> <tr> <td class="alt2" style="border-style: none solid solid; border-color: -moz-use-text-color rgb(255, 255, 255) rgb(255, 255, 255); border-width: 0px 1px 1px;">

</td> <td class="alt1" style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(255, 255, 255) rgb(255, 255, 255) -moz-use-text-color; border-width: 0px 1px 1px 0px;" align="right"> <!-- controls -->

</td></tr></tbody></table>

 

[Andrew Poulsom]

Like this?

Sub Test()
    Dim Sh As Worksheet
    Set Sh = Worksheets.Add
    Dim i As Long
'   Give the sheet a name, today's date in this example
    On Error Resume Next
    For i = 65 To 90
        Sh.Name = Format(Date, "yyyy-mmm-dd") & Chr(i)
        If Err.Number = 0 Then
            Exit For
        Else
            Err.Clear
        End If
    Next i
    On Error GoTo 0
End Sub

 

[villy]

Try this trick:

Sub changer()
Dim sh As Worksheet
i = 1
Worksheets.Add after:=Sheets(Sheets.Count)
For Each sh In Worksheets
    sh.Name = Format(Date, "yyyy-mmm-dd") & "(" & Mid(sh.Columns(i).Address, 2, 1) & ")"
    i = i + 1
Next sh
If Err.Number > 0 Then
MsgBox "Change the name of Sheet : " & sh.Name & " manually"
Err.Clear
End If
On Error GoTo 0
End Sub

 

[tintin1012000]

This is my current code,

How do i marry your code into mine ?

Sub Insert_Sheet_Template()
Dim sh As Worksheet
Dim shName As String

'name of the sheet template
shName = "qualitycircletemplate.xlt"

'Insert sheet template
With ThisWorkbook
Set sh = Sheets.Add(Type:=Application.TemplatesPath & shName, _
after:=.Sheets(.Sheets.Count))
End With

'Give the sheet a name, today's date in this example
On Error Resume Next
sh.Name = Format(Date, "yyyy-mmm-dd")
If Err.Number > 0 Then
MsgBox "Change the name of Sheet : " & sh.Name & " manually"
Err.Clear
End If
On Error GoTo 0
End Sub

 

[Andrew Poulsom]

Try:

Sub Insert_Sheet_Template()
    Dim sh As Worksheet
    Dim shName As String
    Dim i As Long
'   name of the sheet template
    shName = "qualitycircletemplate.xlt"
'   Insert sheet template
    With ThisWorkbook
        Set sh = Sheets.Add(Type:=Application.TemplatesPath & shName, _
            after:=.Sheets(.Sheets.Count))
    End With
'   Give the sheet a name, today's date in this example
    On Error Resume Next
    For i = 65 To 90
        sh.Name = Format(Date, "yyyy-mmm-dd") & Chr(i)
        If Err.Number = 0 Then
            Exit For
        Else
            Err.Clear
        End If
    Next i
    On Error GoTo 0
End Sub

 

[tintin1012000]

Works perfect, thanks very much for your help