Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add new sheets with week ending dates
Is there a way I can add 51 new sheets to a workbook that names those sheets
with a week ending date? Sheet 1 is currently names WE 110604; I would like an additional 51 sheets with each new week ending date. Sheet 2 would be WE 111304, Sheet 3 would be WE 112004, and so on. I appreciate the help, thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add new sheets with week ending dates
Hi Cottage6,
Try: Sub Tester01() Dim i As Long Dim dte As Date dte = CDate("6 Nov 2004") For i = 1 To 51 dte = dte + 7 Sheets.Add after:=Sheets(Sheets.Count) ActiveSheet.Name = "WE " & Format(dte, "mmddyy") Next End Sub --- Regards, Norman "cottage6" wrote in message ... Is there a way I can add 51 new sheets to a workbook that names those sheets with a week ending date? Sheet 1 is currently names WE 110604; I would like an additional 51 sheets with each new week ending date. Sheet 2 would be WE 111304, Sheet 3 would be WE 112004, and so on. I appreciate the help, thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add new sheets with week ending dates
Here is one way:
' Creates sheets in reverse order so sheets are in ascending order when finished Sub WeekMake() StartDate = #11/6/2004# 'Calculate last Week mydays = 51 * 7 'Establish ending week EndWeek = StartDate + mydays 'Add 1 more week to last week so for loop works WeekEnding = EndWeek + 7 For s = 1 To 51 Sheets.Add WeekEnding = WeekEnding - 7 SheetName = "WE " & Month(WeekEnding) & Day(WeekEnding) & Right(Year(WeekEnding), 2) ActiveSheet.Name = SheetName Next s End Sub "cottage6" wrote in message ... Is there a way I can add 51 new sheets to a workbook that names those sheets with a week ending date? Sheet 1 is currently names WE 110604; I would like an additional 51 sheets with each new week ending date. Sheet 2 would be WE 111304, Sheet 3 would be WE 112004, and so on. I appreciate the help, thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert regular Date to Week Ending or Week Beginning Dates | Excel Discussion (Misc queries) | |||
Converting Dates to Week Ending time period | Excel Worksheet Functions | |||
Entering week ending dates | Excel Discussion (Misc queries) | |||
How to format cells to show dates as the week-ending date of that | Excel Worksheet Functions | |||
Week ending | Excel Worksheet Functions |