Thread: Automatically Create & Name New Worksheet Based On Cell Value (date)

I have this figuered out, KIND OF, and need some help.

I am trying to create a new worksheet for every cell value.

Sub AddMonthlySheets()
Dim mMonth As Range
 For Each mMonth In Sheets(1).Range("A1:A12")
    ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count)
     ActiveSheet.Name = mMonth
 Next
End Sub

But this code - kind of works.

If you look at my attached file NewWorksheetTEST.xlsm , it works when the data is in 'January' format. But I need my data to be in 'mmm-yyyy' format (Jan-2012). Once I chnage the format, it doesnt work.

I think its because January is text format and Jan-2012 is a date format, but I dont know how to update my code to accommodate the date format.

Any suggestions would be greatly appreciated.

Thanks in advance.

I revised your code a little bit

Sub AddMonthlySheets()
Application.ScreenUpdating = False
Dim mMonth As Range
 For Each mMonth In Sheets(1).Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count)
     ActiveSheet.Name = Left(mMonth, 3) & "-2011"
 Next

Worksheets("Sheet1").Select
Application.ScreenUpdating = True
End Sub

The values in your workbook in Column A are "text" despite that the formatting was custom. Date formatting does not work with text, and that was where my assumption coming from.