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

 

[Roderick_E]

Assuming each sheet has the date you want in A1 of each sheet:

sub loopsheets
Dim Current As Worksheet


         ' Loop through all of the worksheets in the active workbook.
         For Each Current In Worksheets
if current.visible = true 'only visible sheets
current.name = current.range("A1")
endif
           
         Next
end sub

 

[mumps]

Try:

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

 

[mrjinx007]

This one is giving me "Mismatch".

 

[mrjinx007]

That gave me a "Syntax" error on the 2nd line.

 

[mumps]

I'm not sure which macro you are referring to. If you are using the one I suggested, make sure that the sheet with the date in A1 is the active sheet when you run the macro.

 

[mrjinx007]

Thanks mumps. It gave me a runtime error this time and froze up. Waiting for it to shutdown and try again.

Well I put it on a new form with just 12 tabs and it worked fine. Could it be because I have additional sheets there?

 

[mumps]

You are very welcome. It will work regardless of the number of sheets. It will simply increase the month by 1 for each sheet.

 

[mrjinx007]

Okay, so, is it possible that I have this formula in the sheets that follow the first one have this formula

=EDATE(Nov!AB2,1)

in them to do the same thing? Also, anyways to have the tab name show just the month?

 

[mrjinx007]

Okay,
This time it worked just fine. However, it changed all the (25) tab names to dates instead of the 11 tabs in front sheet that the code is written on.