Copying a Template and rename worksheets from a range in main worksheet as date

[adutchbrew]

I am new to vba. Trying to write code to generate copies from a template worksheet and name the copies from a range in primary worksheet. The ws tabs should be in a date format. So far I was able to generate the copies from the template and it makes the amount of copies in the range, but instead of the ws tabs showing as the date, they are named Template (2) to Template.
Here is the code I wrote that works:

Sub DuplicateAndRenameSheets()


Dim wsMaster As Worksheet
Dim wsTemp As Worksheet
Dim shNames As Range
Dim Nm As Range


Application.ScreenUpdating = 0
With ThisWorkbook
Set wsTemp = .Sheets("Template")
Set wsMaster = .Sheets("Master")

Set shNames = wsMaster.Range("A4:A10") 'The range is formatted as eg Jan 28, Jan 29 etc.

For Each Nm In shNames
wsTemp.Copy After:=.Sheets(.Sheets.Count)
' Nm.Name = Format(Date,"mmmdd") This is one of the code lines I tried but doesn't work
Next Nm

End With

Application.ScreenUpdating = 1


End Sub


Any help is much appreciated

 

[My Aswer Is This]

Why not try something like this:

This script will make you 10 copies to the sheet named "Template"
And name the new sheets beginning with todays date.
And formatted as "mmdd"

No need putting dates in a range on your sheet.

Sub Copy_Template()
Application.ScreenUpdating = False
'Modified 2-1-18 1:15 AM EST
Dim i As Long
For i = 1 To 10
    Sheets("Template").Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = Format(DateAdd("d", i - 1, Date), "mmdd")
Next
Application.ScreenUpdating = True
End Sub

 

[adutchbrew]

Thank you for responding My Answer Is This.
However I would need the code to be able to name the worksheets based on the range in the column. That column range can change (I would use End.xlDown). I used the ("A4:A10") range for simplicity sake in my example.
The way I wrote the code, it will generate the amount of worksheets, but it isn't naming the worksheets according to the name in the cell it's copying from. It will just name the worksheets Template (2), Template (3) etc. Your example does name the worksheets as date. Anyway the code can be amended to copy it from the specified range?

Thanks in the mean time.

 

[My Aswer Is This]

Try this:

You must have sheet names like:
1/28/16
1/29/18
1/30/18

In sheet "Master" column "A" starting in row(4) and script will continue down to lastrow with data.

The script will make a copy of sheet named "Template" and name the sheets from the values in column A of sheet named Master

Sub Copy_Template()
'Modified 2-2-18 12:45 AM EST
Application.ScreenUpdating = False
On Error GoTo M
Dim i As Long
Dim Lastrow As Long
Lastrow = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row
        For i = 4 To Lastrow
            Sheets("Template").Copy After:=Sheets(Sheets.Count)
            ActiveSheet.Name = Format(Sheets("Master").Cells(i, 1).Value, "mmmdd")
        Next
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "That sheet name may already exist or we had some other problem"
Application.ScreenUpdating = True
End Sub

 

[adutchbrew]

That worked perfectly. Starting to really love Excel vba, although it will take a while before I get a good handle on it. Still somewhat intimidating. Much appreciated.

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

That worked perfectly. Starting to really love Excel vba, although it will take a while before I get a good handle on it. Still somewhat intimidating. Much appreciated.