2nd name tab to show next month from first tab name

[mrjinx007]

Hi,
I have this macro that works fine; it copies the date from a cell in that sheet to the tab name.

[COLOR=#000000][FONT=Menlo]Sub myTabName()[/FONT][/COLOR]    ActiveSheet.Name = ActiveSheet.Range("A1") [COLOR=#000000][FONT=Menlo]End Sub[/FONT][/COLOR]

How can I modify it so it can change the other 11 tabs so I have a full year of monthly tabs in sequence?
Thanks

 

[mumps]

Glad it worked out.

 

[mrjinx007]

Hi... I ran to another problem. Initially it worked just fine but now the Q1data1 has been replaced by 9/1/2018, it no longer recognize the date. Should I change the code to

 If ws.Name "9/1/2018" Then

instead of

 If ws.Name Like "*data*" Then

??
Thank you.

 

[mumps]

I think that it would be easier to help and test possible solutions if I could work with your actual file which includes the macros you are currently using. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do using a few examples from your data and referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your September sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. When you change the date in cell AB2 and exit the cell, the 12 sheets starting with the September sheet will all be automatically renamed using the first 3 letters of the month. I hope I understood correctly what you want to do.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("AB2")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim x As Long, y As Long
    y = 0
    For x = 11 To 22
        Sheets(x).Name = Replace(DateAdd("m", y, Range("AB2").Value), "/", "-")
        Sheets(x).Name = Left(MonthName(Month(Sheets(x).Name)), 3)
        y = y + 1
    Next x
    Application.ScreenUpdating = True
End Sub

 

[mrjinx007]

Thank you very much; I am grateful. It worked wonderfully.

 

[mumps]

My pleasure.

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("AB2")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim x As Long, y As Long
    y = 0
    For x = 11 To 22
        Sheets(x).Name = "Sheet" & x
    Next x
    For x = 11 To 22
        Sheets(x).Name = "Sheet" & x
        Sheets(x).Name = Replace(DateAdd("m", y, Range("AB2").Value), "/", "-")
        Sheets(x).Name = Left(MonthName(Month(Sheets(x).Name)), 3)
        y = y + 1
    Next x
    Application.ScreenUpdating = True
End Sub

 

[mrjinx007]

Perfect........... Thank you very much.

 

[mumps]

You are very welcome.

Please delete the line in red. I forgot to remove it in my testing.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("AB2")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim x As Long, y As Long
    y = 0
    For x = 11 To 22
        Sheets(x).Name = "Sheet" & x
    Next x
    For x = 11 To 22
       [COLOR="#FF0000"] Sheets(x).Name = "Sheet" & x
[/COLOR]        Sheets(x).Name = Replace(DateAdd("m", y, Range("AB2").Value), "/", "-")
        Sheets(x).Name = Left(MonthName(Month(Sheets(x).Name)), 3)
        y = y + 1
    Next x
    Application.ScreenUpdating = True
End Sub