Sheet name change

Torrez74

New Member
Joined
Jul 30, 2010
Messages
28
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.
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
Hi and welcome to the board!!
Maybe something like this??
Code:
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

New Member
Joined
Jul 30, 2010
Messages
28
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

New Member
Joined
Jul 30, 2010
Messages
28
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

New Member
Joined
Jul 30, 2010
Messages
28
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

Legend
Joined
Jun 19, 2002
Messages
63,650
You could shorten that:

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

Watch MrExcel Video

Forum statistics

Threads
1,109,120
Messages
5,526,935
Members
409,730
Latest member
mimipiz

This Week's Hot Topics

Top