ExcelBanter - Changing mulitple tab names

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Changing mulitple tab names (https://www.excelbanter.com/excel-worksheet-functions/141289-changing-mulitple-tab-names.html)

Changing mulitple tab names

Is there a way of changing tab names in one or two steps? I have 52 sheets
each one carrying last years names for each week: May 1-7; May 8-15 etc.

Can they be changed in a group to the new dates for 2007-2008: April 30-May
6; May 7-14 etc.?

RD

Changing mulitple tab names

One way:

Public Sub RenameByWeeks()
Dim vResult As Variant
Dim dFirstDate As Double
Dim i As Long

Do
vResult = Application.InputBox( _
Prompt:="Enter year:", _
Type:=1, _
Title:="Rename Worksheets by weeks", _
Default:=Year(Date) + 1)
If vResult = False Then Exit Sub 'user cancelled
Loop Until (vResult 1904) And (vResult <= 9999)
dFirstDate = DateSerial(vResult, 1, 1)
dFirstDate = dFirstDate - WeekDay(dFirstDate) + 1
With ActiveWorkbook.Worksheets
.Item(1).Name = "Jan 1-" & Format(dFirstDate + 6, "d")
For i = 2 To .Count
dFirstDate = dFirstDate + 7
.Item(i).Name = Format(dFirstDate, "mmm d\-") & _
Format(dFirstDate + 6, "d")
Next i
End With
End Sub

In article ,
"RD" wrote:

Is there a way of changing tab names in one or two steps? I have 52 sheets
each one carrying last years names for each week: May 1-7; May 8-15 etc.

Can they be changed in a group to the new dates for 2007-2008: April 30-May
6; May 7-14 etc.?

RD

Changing mulitple tab names

OK, pretty close to what I want, thank you. I have not used VBA before, but
I have managed to make some changes in order to have the first sheet
starting at April 30 - May 6, the second sheet needs to May 7 - 13, then May
14 - 20, etc. The closest I could get was:

Public Sub RenameByWeeks()
Dim vResult As Variant
Dim dFirstDate As Double
Dim i As Long

Do
vResult = Application.InputBox( _
Prompt:="Enter year:", _
Type:=1, _
Title:="Rename Worksheets by weeks", _
Default:=Year(Date) + 1)
If vResult = False Then Exit Sub 'user cancelled
Loop Until (vResult 1904) And (vResult <= 9999)
dFirstDate = DateSerial(2007, 4, 29)
dFirstDate = dFirstDate - Weekday(dFirstDate) + 2
With ActiveWorkbook.Worksheets
.Item(1).Name = "Apr 30-" & Format(dFirstDate + 6, "d")
For i = 2 To .Count
dFirstDate = dFirstDate + 7
.Item(i).Name = Format(dFirstDate + 1, "mmm d\-") & _
Format(dFirstDate + 7, "d")
Next i
End With
End Sub

This gives me Apr 30 - May 6, May 8 - 14, May 15 - 21, etc. (just a little
off... :)

RD

"JE McGimpsey" wrote in message
...
One way:

Public Sub RenameByWeeks()
Dim vResult As Variant
Dim dFirstDate As Double
Dim i As Long

Do
vResult = Application.InputBox( _
Prompt:="Enter year:", _
Type:=1, _
Title:="Rename Worksheets by weeks", _
Default:=Year(Date) + 1)
If vResult = False Then Exit Sub 'user cancelled
Loop Until (vResult 1904) And (vResult <= 9999)
dFirstDate = DateSerial(vResult, 1, 1)
dFirstDate = dFirstDate - WeekDay(dFirstDate) + 1
With ActiveWorkbook.Worksheets
.Item(1).Name = "Jan 1-" & Format(dFirstDate + 6, "d")
For i = 2 To .Count
dFirstDate = dFirstDate + 7
.Item(i).Name = Format(dFirstDate, "mmm d\-") & _
Format(dFirstDate + 6, "d")
Next i
End With
End Sub

In article ,
"RD" wrote:

Is there a way of changing tab names in one or two steps? I have 52
sheets
each one carrying last years names for each week: May 1-7; May 8-15 etc.

Can they be changed in a group to the new dates for 2007-2008: April
30-May
6; May 7-14 etc.?

RD

Changing mulitple tab names

You made a few too many changes:

dFirstDate = DateSerial(vResult, 4, 30)
dFirstDate = dFirstDate - WeekDay(dFirstDate) + 2
With ActiveWorkbook.Worksheets
.Item(1).Name = "Apr 30-" & Format(dFirstDate + 6, "d")
For i = 2 To .Count
dFirstDate = dFirstDate + 7
.Item(i).Name = Format(dFirstDate, "mmm d\-") & _
Format(dFirstDate + 6, "d")
Next i
End With

Note that the first sheet, depending on the year, will be titled

Apr 30-30

or

Apr 30-x

where x is the first Sunday in May. That shouldn't be a hard fix...,
depending on what result you want.

In article ,
"RD" wrote:

OK, pretty close to what I want, thank you. I have not used VBA before, but
I have managed to make some changes in order to have the first sheet
starting at April 30 - May 6, the second sheet needs to May 7 - 13, then May
14 - 20, etc. The closest I could get was:

Public Sub RenameByWeeks()
Dim vResult As Variant
Dim dFirstDate As Double
Dim i As Long

Do
vResult = Application.InputBox( _
Prompt:="Enter year:", _
Type:=1, _
Title:="Rename Worksheets by weeks", _
Default:=Year(Date) + 1)
If vResult = False Then Exit Sub 'user cancelled
Loop Until (vResult 1904) And (vResult <= 9999)
dFirstDate = DateSerial(2007, 4, 29)
dFirstDate = dFirstDate - Weekday(dFirstDate) + 2
With ActiveWorkbook.Worksheets
.Item(1).Name = "Apr 30-" & Format(dFirstDate + 6, "d")
For i = 2 To .Count
dFirstDate = dFirstDate + 7
.Item(i).Name = Format(dFirstDate + 1, "mmm d\-") & _
Format(dFirstDate + 7, "d")
Next i
End With
End Sub

This gives me Apr 30 - May 6, May 8 - 14, May 15 - 21, etc. (just a little
off... :)

RD

"JE McGimpsey" wrote in message
...
One way:

Public Sub RenameByWeeks()
Dim vResult As Variant
Dim dFirstDate As Double
Dim i As Long

Do
vResult = Application.InputBox( _
Prompt:="Enter year:", _
Type:=1, _
Title:="Rename Worksheets by weeks", _
Default:=Year(Date) + 1)
If vResult = False Then Exit Sub 'user cancelled
Loop Until (vResult 1904) And (vResult <= 9999)
dFirstDate = DateSerial(vResult, 1, 1)
dFirstDate = dFirstDate - WeekDay(dFirstDate) + 1
With ActiveWorkbook.Worksheets
.Item(1).Name = "Jan 1-" & Format(dFirstDate + 6, "d")
For i = 2 To .Count
dFirstDate = dFirstDate + 7
.Item(i).Name = Format(dFirstDate, "mmm d\-") & _
Format(dFirstDate + 6, "d")
Next i
End With
End Sub

In article ,
"RD" wrote:

Is there a way of changing tab names in one or two steps? I have 52
sheets
each one carrying last years names for each week: May 1-7; May 8-15 etc.

Can they be changed in a group to the new dates for 2007-2008: April
30-May
6; May 7-14 etc.?

RD

Changing mulitple tab names

Thanks, it worked perfectly. You guys aren't paid enough! :)

"JE McGimpsey" wrote in message
...
You made a few too many changes:

dFirstDate = DateSerial(vResult, 4, 30)
dFirstDate = dFirstDate - WeekDay(dFirstDate) + 2
With ActiveWorkbook.Worksheets
.Item(1).Name = "Apr 30-" & Format(dFirstDate + 6, "d")
For i = 2 To .Count
dFirstDate = dFirstDate + 7
.Item(i).Name = Format(dFirstDate, "mmm d\-") & _
Format(dFirstDate + 6, "d")
Next i
End With

Note that the first sheet, depending on the year, will be titled

Apr 30-30

or

Apr 30-x

where x is the first Sunday in May. That shouldn't be a hard fix...,
depending on what result you want.

In article ,
"RD" wrote:

OK, pretty close to what I want, thank you. I have not used VBA before,
but
I have managed to make some changes in order to have the first sheet
starting at April 30 - May 6, the second sheet needs to May 7 - 13, then
May
14 - 20, etc. The closest I could get was:

Public Sub RenameByWeeks()
Dim vResult As Variant
Dim dFirstDate As Double
Dim i As Long

Do
vResult = Application.InputBox( _
Prompt:="Enter year:", _
Type:=1, _
Title:="Rename Worksheets by weeks", _
Default:=Year(Date) + 1)
If vResult = False Then Exit Sub 'user cancelled
Loop Until (vResult 1904) And (vResult <= 9999)
dFirstDate = DateSerial(2007, 4, 29)
dFirstDate = dFirstDate - Weekday(dFirstDate) + 2
With ActiveWorkbook.Worksheets
.Item(1).Name = "Apr 30-" & Format(dFirstDate + 6, "d")
For i = 2 To .Count
dFirstDate = dFirstDate + 7
.Item(i).Name = Format(dFirstDate + 1, "mmm d\-") & _
Format(dFirstDate + 7, "d")
Next i
End With
End Sub

This gives me Apr 30 - May 6, May 8 - 14, May 15 - 21, etc. (just a
little
off... :)

RD

"JE McGimpsey" wrote in message
...
One way:

Public Sub RenameByWeeks()
Dim vResult As Variant
Dim dFirstDate As Double
Dim i As Long

Do
vResult = Application.InputBox( _
Prompt:="Enter year:", _
Type:=1, _
Title:="Rename Worksheets by weeks", _
Default:=Year(Date) + 1)
If vResult = False Then Exit Sub 'user cancelled
Loop Until (vResult 1904) And (vResult <= 9999)
dFirstDate = DateSerial(vResult, 1, 1)
dFirstDate = dFirstDate - WeekDay(dFirstDate) + 1
With ActiveWorkbook.Worksheets
.Item(1).Name = "Jan 1-" & Format(dFirstDate + 6, "d")
For i = 2 To .Count
dFirstDate = dFirstDate + 7
.Item(i).Name = Format(dFirstDate, "mmm d\-") & _
Format(dFirstDate + 6, "d")
Next i
End With
End Sub

In article ,
"RD" wrote:

Is there a way of changing tab names in one or two steps? I have 52
sheets
each one carrying last years names for each week: May 1-7; May 8-15
etc.

Can they be changed in a group to the new dates for 2007-2008: April
30-May
6; May 7-14 etc.?

RD