Sheet name change

[Torrez74]

Hello all I need a bit of help with a workbook I'm working on please.

In the string;

Sheets("7-29").Select
Sheets("7-29").Name = "8-26"
Sheets("7-30").Select
Sheets("7-30").Name = "8-27"
Sheets("7-31").Select
Sheets("7-31").Name = "8-28"
Sheets("8-1").Select
Sheets("8-1").Name = "8-29"

I am changing the sheet names and they all are for ascending calender days. I would like to add a textbox before the string (i have the code for that) where you are asked "What is the first day of the period?". Where I need help is How do I make that answer the name of the first sheet and then change each sheet after to be in the same sequence?

Thanks for your help.

 

[lenze]

Hi and welcome to the board!!
Maybe something like this??

Sub RenameSheets()
x = InputBox("What is the 1st day?")
For i = 1 To Sheets.Count
Sheets(i).Name = "8-" & x
x = x + 1
Next i
End Sub

lenze

 

[Torrez74]

I like it, i was thinking of directing them to input the day in the format MM/DD that way the whole string can be the name?? any thoughts?

 

[Torrez74]

OK so Ive been playing with this code, When it hit me that in the book I'm working on the first three sheets are constant but have all the dates for the Period in a row. Should I just write a Macro to rename the sheets according to the dates in that column? And can anyone point me to an example of that??

 

[Torrez74]

I just wanted to show how I got it done. I used the code;

Sheets(37).Select
sDay = Format(Range("A1").Value, "mm-dd")
ActiveSheet.Name = sDay

Sheets(36).Select
sDay = Format(Range("A1").Value, "mm-dd")
ActiveSheet.Name = sDay

Sheets(35).Select
sDay = Format(Range("A1").Value, "mm-dd")
ActiveSheet.Name = sDay

I have "A1" automaticly update and I go backwords so there should never be 2 sheet with the same name (it'll jam up)

 

[VoG]

You could shorten that:

For i = 37 To 35 Step -1
    With Sheets(i)
        .Name = Format(.Range("A1").Value, "mm-dd")
    End With
Next i