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.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,713
Messages
5,833,269
Members
430,200
Latest member
ADLHMA2022

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top