renaming worksheet tabs

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How can I rename the worksheet tabs at one time. EXAMPLE: I created a months
work of worksheets for the month of JANUARY. Now I need to change them to
FEBRUARY. Jan-1,Jan-2,Jan3 to Feb-1,Feb2,Feb3 without ahve to go to each tab.

Can it be done?
 
try this
Sub changewsname()
newmonth = InputBox("Enter 3 letter code for month desired")
For Each ws In Worksheets
If Left(ws.Name, 5) <> "Sheet" _
And IsNumeric(Right(ws.Name, 1)) Then
ws.Name = Application.Proper _
(newmonth & Right(ws.Name, Len(ws.Name) - 3))
End If
Next ws
End Sub
 
Sub namesheets()
Dim i As Date
i = DateValue("Feb-1-2006")
For Each ws In Worksheets
ws.Name = Format(i, "mmm-d")
i = i + 1
Next
End Sub

I'll leave you to decide what to do with the 3 leftover sheets Jan-29, Jan-30
and Jan-31


Gord Dibben Excel MVP
 
Gord,

I'm still lost. I do not know where to insert this formula in an excel
sheet.

Help...
 
It is not a worksheet formula. It is a VBA macro.

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the namesheets(or Don's changewsname)
code in there. Save the workbook and hit ALT + Q to return to your workbook.

Run the macro by going to Tool>Macro>Macros.

To assign to a button on your Toolbar........

Tools>Customize>Commands

Scroll down to and select Macros.

Drag the smiley face button to a Toolbar. Right-click on it and "assign
macro".

Pick the namesheets(or Don's) macro and OK.


Gord
 
Back
Top