rename tabs
 

hi,

I have a spreadsheet containing 30 tabs--named according to the date such as
01.02.07, 02.02.07....28.02.07. However, it will be painful to manualy change
the name of them into 01.03.07, 02.03.07 for the next month. Is there an easy
way to do it by using VBA?

Thanks for any help in advance.

rename tabs
 

Sub next_month()
Dim v As String
For Each w In Worksheets
v = w.Name
v = Left(v, 3) & "03" & Right(v, 3)
w.Name = v
Next
End Sub

but remember that March will have moe days then February.
--
Gary's Student
gsnu200704


"May" wrote:

hi,

I have a spreadsheet containing 30 tabs--named according to the date such as
01.02.07, 02.02.07....28.02.07. However, it will be painful to manualy change
the name of them into 01.03.07, 02.03.07 for the next month. Is there an easy
way to do it by using VBA?

Thanks for any help in advance.

rename tabs
 

Hi Gary,
Sorry for the late reply. I'm a new member to this community and it really
takes me some time to find out my question again.

thanks for your help, however it didn't work due to the following error:

"w"---compile error. variable not defined.

"Gary''s Student" wrote:

Sub next_month()
Dim v As String
For Each w In Worksheets
v = w.Name
v = Left(v, 3) & "03" & Right(v, 3)
w.Name = v
Next
End Sub

but remember that March will have moe days then February.
--
Gary's Student
gsnu200704


"May" wrote:

hi,

I have a spreadsheet containing 30 tabs--named according to the date such as
01.02.07, 02.02.07....28.02.07. However, it will be painful to manualy change
the name of them into 01.03.07, 02.03.07 for the next month. Is there an easy
way to do it by using VBA?

Thanks for any help in advance.

rename tabs
 

You need to declare the variable 'w':

Dim W As Worksheet



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

"May" wrote in message
...
Hi Gary,
Sorry for the late reply. I'm a new member to this community and it really
takes me some time to find out my question again.

thanks for your help, however it didn't work due to the following error:

"w"---compile error. variable not defined.

"Gary''s Student" wrote:

Sub next_month()
Dim v As String
For Each w In Worksheets
v = w.Name
v = Left(v, 3) & "03" & Right(v, 3)
w.Name = v
Next
End Sub

but remember that March will have moe days then February.
--
Gary's Student
gsnu200704


"May" wrote:

hi,

I have a spreadsheet containing 30 tabs--named according to the date
such as
01.02.07, 02.02.07....28.02.07. However, it will be painful to manualy
change
the name of them into 01.03.07, 02.03.07 for the next month. Is there
an easy
way to do it by using VBA?

Thanks for any help in advance.

rename tabs
 

I just added it in but the following error popped up:
Run-time error "1004"
cannot rename a sheet to the same name as another sheet, a reference library
or a workbook referenced by Visual Basic.

"Chip Pearson" wrote:

You need to declare the variable 'w':

Dim W As Worksheet



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

"May" wrote in message
...
Hi Gary,
Sorry for the late reply. I'm a new member to this community and it really
takes me some time to find out my question again.

thanks for your help, however it didn't work due to the following error:

"w"---compile error. variable not defined.

"Gary''s Student" wrote:

Sub next_month()
Dim v As String
For Each w In Worksheets
v = w.Name
v = Left(v, 3) & "03" & Right(v, 3)
w.Name = v
Next
End Sub

but remember that March will have moe days then February.
--
Gary's Student
gsnu200704


"May" wrote:

hi,

I have a spreadsheet containing 30 tabs--named according to the date
such as
01.02.07, 02.02.07....28.02.07. However, it will be painful to manualy
change
the name of them into 01.03.07, 02.03.07 for the next month. Is there
an easy
way to do it by using VBA?

Thanks for any help in advance.

rename tabs
 

May,

Try something like the following:

Sub NameSheets()
Dim Ndx As Long
Dim StartMonth As Variant
StartMonth = Application.InputBox(Prompt:="Enter the month number.",
Type:=1)
If StartMonth = False Then
Exit Sub
End If
For Ndx = 1 To ThisWorkbook.Worksheets.Count
ThisWorkbook.Worksheets(Ndx).Name = Format(DateSerial( _
IIf(StartMonth = 1, Year(Now) + 1, Year(Now)), StartMonth, Ndx), _
"dd.mm.yy")
Next Ndx
End Sub

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"May" wrote in message
...
hi,

I have a spreadsheet containing 30 tabs--named according to the date such
as
01.02.07, 02.02.07....28.02.07. However, it will be painful to manualy
change
the name of them into 01.03.07, 02.03.07 for the next month. Is there an
easy
way to do it by using VBA?

Thanks for any help in advance.