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]

Are the 12 sheets named "Sheet1", "Sheet2", "Sheet3", ….. "Sheet12"?

 

[mrjinx007]

No, Q1data1, Q1data2, Q1data3 This represent the first quarter, then Q2data1, Q2data2,Q3data3, and so on to Q4data3

 

[mumps]

Do the names of any of the other sheets contain the word "data"?

 

[mrjinx007]

No.

 

[mumps]

Try:

Sub myTabName()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, x As Long
    x = 0
    For Each ws In Sheets
        If ws.Name Like "*data*" Then
            ws.Name = Replace(DateAdd("m", x, Range("A1").Value), "/", "-")
            x = x + 1
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[mrjinx007]

OMG..... It worked beautifully. Thank you very much for your help.

 

[mumps]

You are very welcome.

 

[mrjinx007]

Good morning. I am still having an issue with this that I thought I could solve but can't. The dates on the tabs still show as month day and year (9/1/2018). I tried to change the code part "m":

[COLOR=#333333]Replace(DateAdd("m"[/COLOR]

to mm, yyyy and so on and the name tabs remain the same. This not a big issue other than makes the tabs too big.
Thanks

 

[mumps]

The "m" doesn't affect the format of the cell. The macro increases the tab name by one month at a time. The "m" simply tells the macro to increase by month rather than day or year. Try re-formatting the cells that have dates to the format that you want.

 

[mrjinx007]

Thanks for the reply. I tried that as well and still showed the month day and year. It is not a big deal; I am very happy not having to change the tab names manually every year.