creating tabs from a summary list of dates

[ammartino44]

Hello. I have in tab 1 a list of months going down column A. They are formatted Jan-12, Feb-12 etc. I would like a macro that creates a tab for each of these months but in the format Jan 2012, Feb 2012, etc. Help would be appreciated

[JOHN H. DAVIS]

Maybe:

Sub ammartino44()
Dim i As Long
Dim x As Date
Dim ws As Worksheet
Set ws = ActiveSheet
For i = Range("A" & Rows.Count).End(3)(1).Row To 2 Step -1
    x = Range("A" & i).Value
    Sheets.Add.Name = Format(x, "mmm yyyy")
    ws.Activate
Next i
End Sub

[ammartino44]

Sweet. This worked. Could you explain what each piece of the vba code is doing (I like to understand and learn)? Also, what if I wanted the order to be from oldest date tab on the left to newest date tab on the right? Thanks.

[JOHN H. DAVIS]

ammartino44:

Per your PM

I used three variables:

Dim i As Long
Dim x As Date
Dim ws As Worksheet

i - is used to increment operations from the bottom row of Column A to 2 (there is Step -1) which tells it too increment one step at a time upwards. (ie For i = Range("A" & Rows.Count).End(3)(1).Row To 2 Step -1)

x - is used to define each incremented value in Column A as a date. This is also used to change the formats to your required date for the added sheets name. (ie x = Range("A" & i).Value
Sheets.Add.Name = Format(x, "mmm yyyy")



ws - is used to identify the activesheet (ie Set ws = ActiveSheet)


ws.Activate - reactivates the activesheet (sheet with the dates)
Next i - increments to the next row.

Hope that helps.

[Leith Ross]

Hello ammartino44,

This should do what you want...

Sub MakeNewTabs()

    Dim Cell    As Range
    Dim Rng     As Range
    Dim RngEnd  As Range
    Dim Wks     As Worksheet
    
        
        Set Wks = Sheet1
        
        Set Rng = Wks.Range("A1")
        Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp)
        Set Rng = Wks.Range(Rng, RngEnd)
        
            For Each Cell In Rng
                If IsDate(Cell) Then
                    Worksheets.Add After:=Worksheets(Worksheets.Count)
                    ActiveSheet.Name = Format(Cell, "mmm yyyy")
                End If
            Next Cell
            
End Sub