rename tabs

M

May

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.
 
G

Gary''s Student

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.
 
M

May

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.
 
C

Chip Pearson

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)
 
M

May

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.
 
C

Chip Pearson

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)
 
Top