renaming worksheet tabs
 

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?

renaming worksheet tabs
 

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

--
Don Guillett
SalesAid Software

"calibronco" wrote in message
...
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?

renaming worksheet tabs
 

Don,
Where exactly do I insert this formula?

calibronco

"Don Guillett" wrote:

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

--
Don Guillett
SalesAid Software

"calibronco" wrote in message
...
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?

renaming worksheet tabs
 

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

On Fri, 25 Nov 2005 14:59:02 -0800, "calibronco"
wrote:

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?

renaming worksheet tabs
 

Gord,

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

Help...

"Gord Dibben" wrote:

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

On Fri, 25 Nov 2005 14:59:02 -0800, "calibronco"
wrote:

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?

renaming worksheet tabs
 

Still lost! I don't know exactly where I insert this formula.

Help...

"Gord Dibben" wrote:

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

On Fri, 25 Nov 2005 14:59:02 -0800, "calibronco"
wrote:

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?

renaming worksheet tabs
 

These are not formulas that go into the worksheet.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

calibronco wrote:

Still lost! I don't know exactly where I insert this formula.

Help...

"Gord Dibben" wrote:

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

On Fri, 25 Nov 2005 14:59:02 -0800, "calibronco"
wrote:

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?



--

Dave Peterson

renaming worksheet tabs
 

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 InsertModule. 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 ToolMacroMacros.

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

ToolsCustomizeCommands

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

On Fri, 25 Nov 2005 16:14:02 -0800, "calibronco"
wrote:

Gord,

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

Help...

"Gord Dibben" wrote:

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

On Fri, 25 Nov 2005 14:59:02 -0800, "calibronco"
wrote:

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?