Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rename all existing worksheet tabs | Excel Discussion (Misc queries) | |||
Tabs accross of bottom of sheet are not showing | Excel Discussion (Misc queries) | |||
RENAME SHEET TABS AOYUMATICALLY | Excel Discussion (Misc queries) | |||
Hidding Tabs | Excel Discussion (Misc queries) | |||
Sheet tabs disappear sometimes in Internet Explorer | Excel Discussion (Misc queries) |