I want to change the name of (52) worksheets.
 

I have copied a workbook that I used in 2006 for a simalar use in 2007. The
workbook has 52 worksheets which names reflect the Sunday at the end of the
week; (Week Ending 1-8-07, Week Ending 1-15-07, Week Ending 1-22-07...)

I would like to add a function that ties all the Tabs to the first Tab. When
I change the name of the first Tab to "Week Ending 1-7-07" I would like the
remaining 51 Tabs to change accordingly.

Can someone give me a suggestion?

Darrell

I want to change the name of (52) worksheets.
 

Dim i As Long
Dim start As Date
With ActiveWorkbook
start = DateValue(Replace(.Worksheets(1).Name, "Week Ending ", ""))
For i = 2 To .Worksheets.Count
Worksheets(i).Name = "Week Ending " & Format(start + (i - 1) *
7, "m-d-yy")
Next i
End With

End Sub


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Dr. Darrell" wrote in message
...
I have copied a workbook that I used in 2006 for a simalar use in 2007. The
workbook has 52 worksheets which names reflect the Sunday at the end of
the
week; (Week Ending 1-8-07, Week Ending 1-15-07, Week Ending 1-22-07...)

I would like to add a function that ties all the Tabs to the first Tab.
When
I change the name of the first Tab to "Week Ending 1-7-07" I would like
the
remaining 51 Tabs to change accordingly.

Can someone give me a suggestion?

Darrell

I want to change the name of (52) worksheets.
 

Thank you Bob. I am not a VBA Guru, so I need to ask; where do I add the
code. I suspect that I add it to the worksheet (Right click on the tab, View
Code, Worksheet).

The following code is already the
**********************************************
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub
**********************************************

Do I add your code before or after the pre-existing code?

"Bob Phillips" wrote:

Dim i As Long
Dim start As Date
With ActiveWorkbook
start = DateValue(Replace(.Worksheets(1).Name, "Week Ending ", ""))
For i = 2 To .Worksheets.Count
Worksheets(i).Name = "Week Ending " & Format(start + (i - 1) *
7, "m-d-yy")
Next i
End With

End Sub


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Dr. Darrell" wrote in message
...
I have copied a workbook that I used in 2006 for a simalar use in 2007. The
workbook has 52 worksheets which names reflect the Sunday at the end of
the
week; (Week Ending 1-8-07, Week Ending 1-15-07, Week Ending 1-22-07...)

I would like to add a function that ties all the Tabs to the first Tab.
When
I change the name of the first Tab to "Week Ending 1-7-07" I would like
the
remaining 51 Tabs to change accordingly.

Can someone give me a suggestion?

Darrell

I want to change the name of (52) worksheets.
 

Nice code Bob;
What is the value of start after running:
Line:
start = DateValue(Replace(.Worksheets(1).Name, "Week Ending ", ""))

What does the last , "")) do?
Thanks,

Jim


"Bob Phillips" wrote in message
:

Dim i As Long
Dim start As Date
With ActiveWorkbook
start = DateValue(Replace(.Worksheets(1).Name, "Week Ending ", ""))
For i = 2 To .Worksheets.Count
Worksheets(i).Name = "Week Ending " & Format(start + (i - 1) *
7, "m-d-yy")
Next i
End With

End Sub


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Dr. Darrell" wrote in message
...
I have copied a workbook that I used in 2006 for a simalar use in 2007. The
workbook has 52 worksheets which names reflect the Sunday at the end of
the
week; (Week Ending 1-8-07, Week Ending 1-15-07, Week Ending 1-22-07...)

I would like to add a function that ties all the Tabs to the first Tab.
When
I change the name of the first Tab to "Week Ending 1-7-07" I would like
the
remaining 51 Tabs to change accordingly.

Can someone give me a suggestion?

Darrell

I want to change the name of (52) worksheets.
 

Jim,

I am using the Replace function, the "" is what the string "Week Ending " is
being replaced by, the first ) closes the Replace call, the second ) closes
the DataValue call.

It simply extracts the date string from the first workbook name, and makes a
real date from that.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"JMay" wrote in message
...
Nice code Bob;
What is the value of start after running:
Line:
start = DateValue(Replace(.Worksheets(1).Name, "Week Ending ", ""))

What does the last , "")) do?
Thanks,

Jim


"Bob Phillips" wrote in message
:

Dim i As Long
Dim start As Date
With ActiveWorkbook
start = DateValue(Replace(.Worksheets(1).Name, "Week Ending ",
""))
For i = 2 To .Worksheets.Count
Worksheets(i).Name = "Week Ending " & Format(start + (i - 1)
*
7, "m-d-yy")
Next i
End With

End Sub


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Dr. Darrell" wrote in message
...
I have copied a workbook that I used in 2006 for a simalar use in 2007.
The
workbook has 52 worksheets which names reflect the Sunday at the end of
the
week; (Week Ending 1-8-07, Week Ending 1-15-07, Week Ending 1-22-07...)

I would like to add a function that ties all the Tabs to the first Tab.
When
I change the name of the first Tab to "Week Ending 1-7-07" I would like
the
remaining 51 Tabs to change accordingly.

Can someone give me a suggestion?

Darrell

I want to change the name of (52) worksheets.
 

You don't add it there at all.

In the VBIDE (Alt-F11), you need to insert a code module (InsertModule),
then paste the code in the code pane that opens up.

Create a macro, by typing say

Sub myMacro()

End Sub

then paste my code aftre the Sub line.

Finally, put the cursor anywhere within the code and run it (F5).

Then return to excel.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Dr. Darrell" wrote in message
...
Thank you Bob. I am not a VBA Guru, so I need to ask; where do I add the
code. I suspect that I add it to the worksheet (Right click on the tab,
View
Code, Worksheet).

The following code is already the
**********************************************
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub
**********************************************

Do I add your code before or after the pre-existing code?

"Bob Phillips" wrote:

Dim i As Long
Dim start As Date
With ActiveWorkbook
start = DateValue(Replace(.Worksheets(1).Name, "Week Ending ",
""))
For i = 2 To .Worksheets.Count
Worksheets(i).Name = "Week Ending " & Format(start + (i - 1)
*
7, "m-d-yy")
Next i
End With

End Sub


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Dr. Darrell" wrote in message
...
I have copied a workbook that I used in 2006 for a simalar use in 2007.
The
workbook has 52 worksheets which names reflect the Sunday at the end of
the
week; (Week Ending 1-8-07, Week Ending 1-15-07, Week Ending 1-22-07...)

I would like to add a function that ties all the Tabs to the first Tab.
When
I change the name of the first Tab to "Week Ending 1-7-07" I would like
the
remaining 51 Tabs to change accordingly.

Can someone give me a suggestion?

Darrell

I want to change the name of (52) worksheets.
 

Bob:

Should the Asterisk be a plus sign?

************************************************** ********
Worksheets(i).Name = "Week Ending " & Format(start + (i - 1) * 7, "m-d-yy")
************************************************** ********

"Bob Phillips" wrote:

Dim i As Long
Dim start As Date
With ActiveWorkbook
start = DateValue(Replace(.Worksheets(1).Name, "Week Ending ", ""))
For i = 2 To .Worksheets.Count
Worksheets(i).Name = "Week Ending " & Format(start + (i - 1) *
7, "m-d-yy")
Next i
End With

End Sub


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Dr. Darrell" wrote in message
...
I have copied a workbook that I used in 2006 for a simalar use in 2007. The
workbook has 52 worksheets which names reflect the Sunday at the end of
the
week; (Week Ending 1-8-07, Week Ending 1-15-07, Week Ending 1-22-07...)

I would like to add a function that ties all the Tabs to the first Tab.
When
I change the name of the first Tab to "Week Ending 1-7-07" I would like
the
remaining 51 Tabs to change accordingly.

Can someone give me a suggestion?

Darrell

I want to change the name of (52) worksheets.
 

No, it is a multiplier so as to increment each by 7, 7, 14, 21, etc., not 8,
9, 10

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Dr. Darrell" wrote in message
...
Bob:

Should the Asterisk be a plus sign?

************************************************** ********
Worksheets(i).Name = "Week Ending " & Format(start + (i - 1) * 7,
"m-d-yy")
************************************************** ********

"Bob Phillips" wrote:

Dim i As Long
Dim start As Date
With ActiveWorkbook
start = DateValue(Replace(.Worksheets(1).Name, "Week Ending ",
""))
For i = 2 To .Worksheets.Count
Worksheets(i).Name = "Week Ending " & Format(start + (i - 1)
*
7, "m-d-yy")
Next i
End With

End Sub


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Dr. Darrell" wrote in message
...
I have copied a workbook that I used in 2006 for a simalar use in 2007.
The
workbook has 52 worksheets which names reflect the Sunday at the end of
the
week; (Week Ending 1-8-07, Week Ending 1-15-07, Week Ending 1-22-07...)

I would like to add a function that ties all the Tabs to the first Tab.
When
I change the name of the first Tab to "Week Ending 1-7-07" I would like
the
remaining 51 Tabs to change accordingly.

Can someone give me a suggestion?

Darrell

I want to change the name of (52) worksheets.
 

Bob:

Thank you very much, this saved me a lot of time and effort.

Darrell

"Bob Phillips" wrote:

No, it is a multiplier so as to increment each by 7, 7, 14, 21, etc., not 8,
9, 10

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Dr. Darrell" wrote in message
...
Bob:

Should the Asterisk be a plus sign?

************************************************** ********
Worksheets(i).Name = "Week Ending " & Format(start + (i - 1) * 7,
"m-d-yy")
************************************************** ********

"Bob Phillips" wrote:

Dim i As Long
Dim start As Date
With ActiveWorkbook
start = DateValue(Replace(.Worksheets(1).Name, "Week Ending ",
""))
For i = 2 To .Worksheets.Count
Worksheets(i).Name = "Week Ending " & Format(start + (i - 1)
*
7, "m-d-yy")
Next i
End With

End Sub


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Dr. Darrell" wrote in message
...
I have copied a workbook that I used in 2006 for a simalar use in 2007.
The
workbook has 52 worksheets which names reflect the Sunday at the end of
the
week; (Week Ending 1-8-07, Week Ending 1-15-07, Week Ending 1-22-07...)

I would like to add a function that ties all the Tabs to the first Tab.
When
I change the name of the first Tab to "Week Ending 1-7-07" I would like
the
remaining 51 Tabs to change accordingly.

Can someone give me a suggestion?

Darrell