VBA - Copy sheet and rename based on cell range

[Nordicrx8]

Hi all!

I'm trying to automate a process. At the start of a new month, I need to create a new sheet for each day of the month. I'd like to press a button and have the sheet do the work for me.

I found this code and got it to work partially - it creates 30 new tabs, but it just increments the number rather than taking the name from the cell range B2:B32. (Which is a date)

Could anyone assist in altering the code so it renames the sheet for me?

Thank you!!

Sub Create_Daily()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = Sheets("Blank")
Set sh2 = Sheets("MTD Performance")
    For Each c In sh2.Range("B2:B32", sh2.Cells(Rows.Count, 2).End(xlUp))
        sh1.Copy After:=Sheets(Sheets.Count)
 
    Next
End Sub

 

[Nordicrx8]

Final Working Code:

Sub Create_Daily()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = Sheets("Blank")
Set sh2 = Sheets("MTD Template")
    For Each c In sh2.Range("AS2:AS32")
        sh1.Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = c.Value
    Next
End Sub

 

[Fluff]

If you want to keep the cells as dates you could use

ActiveSheet.Name = c.Text

 

[Nordicrx8]

Oh neat! Thank you!

 

[Fluff]

You're welcome & thanks for the feedback.