macro to change worksheet names

[haganator]

I'm running windows xp sp3 and using excel 2007.


There are 7 worksheets in this document, and each worksheet has a date for a name. Each week the worksheets are manually updated with the new dates. I would like to have a macro update these worksheets with the new dates. I don't know if it would be easier to use some type of calendar function, or if there is a way to use a pop-up message requesting the starting date to be used. Below is what the worksheet names look like.


Sub ChangeTabName()
'
' ChangeTabName Macro
'

'
Sheets("04-16-2012").Select
Sheets("04-16-2012").Name = "04-23-2012"
Sheets("04-17-2012").Select
Sheets("04-17-2012").Name = "04-24-2012"
Sheets("04-18-2012").Select
Sheets("04-18-2012").Name = "04-25-2012"
Sheets("04-19-2012").Select
Sheets("04-19-2012").Name = "04-26-2012"
Sheets("04-20-2012").Select
Sheets("04-20-2012").Name = "04-27-2012"
Sheets("04-21-2012").Select
Sheets("04-21-2012").Name = "04-28-2012"
Sheets("04-22-2012").Select
Sheets("04-22-2012").Name = "04-29-2012"

End Sub

Any help would be greatly appreciated.

Thanks,
Jeff

 

[FormR]

You could try this;

Sub ChangeTabName() 'Adds 7 days to the sheet names that are dates

Dim Dte As Variant
Dim Sht As Worksheet

On Error Resume Next
    
    For Each Sht In ThisWorkbook.Worksheets
        Dte = Sht.Name
        If IsDate(Dte) Then Sht.Name = Format(DateAdd("d", 7, Dte), "mm-dd-yyyy")
    Next Sht

End Sub

 

[haganator]

That worked beautifully. How could I use this code to change the sheet names if there is more than just the date? For example, how would the above code be used to change a sheet named "04-16-2012 smspm" to the next weeks date?

Thanks again for the first fix.
Jeff

 

[FormR]

That worked beautifully. How could I use this code to change the sheet names if there is more than just the date? For example, how would the above code be used to change a sheet named "04-16-2012 smspm" to the next weeks date?

Thanks again for the first fix.
Jeff

Maybe like this, assuming the date is always the first part of the sheet name.

Sub ChangeTabName() 'Adds 7 days to the sheet names that are dates

Dim Dte As Variant
Dim Sht As Worksheet

On Error Resume Next
    
    For Each Sht In ThisWorkbook.Worksheets
        Dte = Left(Sht.Name, 10)
        If IsDate(Dte) Then
            Sht.Name = Format(DateAdd("d", 7, Dte), "mm-dd-yyyy") _
            & Mid(Sht.Name, 11, Len(Sht.Name) - 10)
        End If
    Next Sht

End Sub

 

[haganator]

Thanks again! It worked great.