VBA help copying worksheet & renaming based on month/year date list

[Hat_Trick]

Hello,
I have a macro I found (I think it was from this forum) that I have been able to successfully use to complete the following operation:

I have a list of names, and it copies a "template" worksheet and renames to each name on the list.

So…that part works. The problem is that I now want to do the same thing, but with a month/year date (Aug-2013, Sept-2013, Oct-2013, etc) in lieu of a name. I need it to be an actual date and not a text string “Aug-2013” because I have a sumifs formula calculating hours by week within that month.

I am getting a 400 error. If I make the list into text (August, September, October, etc) it works just fine, but when I switch it back to a date, it falls on its face. I am sure it is something simple, but it exceeds my very limited knowledge and skills.

Could I please get some help massaging this code so it will work with dates?

Sub Initialsetup()
    Application.ScreenUpdating = False
    Dim bottomA As Integer
    bottomA = Range("A" & Rows.Count).End(xlUp).Row
    Dim c As Range
    Dim ws As Worksheet
       
    For Each c In Range("A2:A" & bottomA)
        Set ws = Nothing
        On Error Resume Next
        Set ws = Worksheets(c.Value)
        On Error GoTo 0
        If ws Is Nothing Then
            Sheets("Month Template").Select
            Sheets("Month Template").Copy Before:=Sheets("Roll Up")
            ActiveSheet.Name = c.Value
        End If
    Next c
    Application.ScreenUpdating = True
      
 End Sub

Thank you in advance for your help.

Hat_Trick

 

[dmt32]

Hi,
try amending the line that names your worksheet as follows & see if helps:

ActiveSheet.Name = Format(c.Value, "mmm-yyyy")

Dave

 

[Hat_Trick]

Hi,
try amending the line that names your worksheet as follows & see if helps:

ActiveSheet.Name = Format(c.Value, "mmm-yyyy")

Dave

Awesome! Thank you. That did the trick. I was headed in that direction, but could not figure out how to make it work.


Hat_Trick