Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum cells in multiple worksheets which change every month | Excel Discussion (Misc queries) | |||
How do you copy and rename linked worksheets? | Excel Discussion (Misc queries) | |||
Globally change print settings for several worksheets at the same | Excel Discussion (Misc queries) | |||
Why do my dates change when I copy them between Excel worksheets? | Excel Worksheet Functions | |||
Protect/unprotect all worksheets | Excel Worksheet Functions |