| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a worksheet for every weekday
I was wondering if it is possible to create a macro in a workbook wher every sheet is renamed to display the date of every weekday. I have to create several spreadsheets at the start of the year and t manually rename each sheet would be time consuming. Is it possible -- hungledin ----------------------------------------------------------------------- hungledink's Profile: http://www.officehelp.in/member.php?userid=556 View this thread: http://www.officehelp.in/showthread.php?t=128881 Posted from - http://www.officehelp.i |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating a worksheet for every weekday
Sub Test2()
Dim dFrom As Date, dTo As Date Dim d As Date, i as Long dFrom = CDate("1/Jan/07") dTo = CDate("31/Dec/07") dTo = dFrom + 30 ' limit for testing Workbooks.Add n = Worksheets.Count - 1 For d = dFrom To dTo If WeekDay(CDate(d), 2) < 6 Then n = n + 1 Worksheets.Add(after:=Worksheets(n)).Name = Format(d, "ddd dd mmm") End If Next Worksheets(1).Activate End Sub There's probably a way to exclude holidays. Regards, Peter T "hungledink" wrote in message ... I was wondering if it is possible to create a macro in a workbook where every sheet is renamed to display the date of every weekday. I have to create several spreadsheets at the start of the year and to manually rename each sheet would be time consuming. Is it possible? -- hungledink ------------------------------------------------------------------------ hungledink's Profile: http://www.officehelp.in/member.php?userid=5563 View this thread: http://www.officehelp.in/showthread.php?t=1288816 Posted from - http://www.officehelp.in |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating a worksheet for every weekday
Thanks for that, it works well. The only thing is it leaves the first three worksheets, Sheet1, Sheet and Sheet3 in the workbook. I have tried to add some code so they ar deleted but it keeps asking me to confirm the delete. Is there a way to stop this confirmation box appearing? The code I have entered is simply been produced by recording th deletion of the three sheets. -Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select Sheets("Sheet3").Activate ActiveWindow.SelectedSheets.Delete -- hungledin ----------------------------------------------------------------------- hungledink's Profile: http://www.officehelp.in/member.php?userid=556 View this thread: http://www.officehelp.in/showthread.php?t=128881 Posted from - http://www.officehelp.i |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating a worksheet for every weekday
Application.DisplayAlerts = False
Sheets(Array("Sheet1", "Sheet2", "Sheet3").Delete Application.DisplayAlerts = True -- Regards, Tom Ogilvy "hungledink" wrote in message ... Thanks for that, it works well. The only thing is it leaves the first three worksheets, Sheet1, Sheet2 and Sheet3 in the workbook. I have tried to add some code so they are deleted but it keeps asking me to confirm the delete. Is there a way to stop this confirmation box appearing? The code I have entered is simply been produced by recording the deletion of the three sheets. -Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select Sheets("Sheet3").Activate ActiveWindow.SelectedSheets.Delete- -- hungledink ------------------------------------------------------------------------ hungledink's Profile: http://www.officehelp.in/member.php?userid=5563 View this thread: http://www.officehelp.in/showthread.php?t=1288816 Posted from - http://www.officehelp.in |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating a worksheet for every weekday
You can temporarily change Application.DisplayAlerts = False / True.
This revised routine avoids deleting any sheets and leaves the 'ordered' sheet codenames intact. Sub Test3() Dim dFrom As Date, dTo As Date Dim d As Date, i As Long dFrom = CDate("1/Jan/07") dTo = CDate("31/Dec/07") dTo = dFrom + 30 ' limit for testing Workbooks.Add n = 0 For d = dFrom To dTo If Weekday(CDate(d), 2) < 6 Then n = n + 1 If Worksheets.Count < n Then Worksheets.Add(after:=Worksheets(n - 1)).Name = Format(d, "yymmmdd ddd") Else Worksheets(n).Name = Format(d, "yymmmdd ddd") End If End If Next Worksheets(1).Activate End Sub I amended the date format from last time but adjust to your needs. Regards, Peter T "hungledink" wrote in message ... Thanks for that, it works well. The only thing is it leaves the first three worksheets, Sheet1, Sheet2 and Sheet3 in the workbook. I have tried to add some code so they are deleted but it keeps asking me to confirm the delete. Is there a way to stop this confirmation box appearing? The code I have entered is simply been produced by recording the deletion of the three sheets. -Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select Sheets("Sheet3").Activate ActiveWindow.SelectedSheets.Delete- -- hungledink ------------------------------------------------------------------------ hungledink's Profile: http://www.officehelp.in/member.php?userid=5563 View this thread: http://www.officehelp.in/showthread.php?t=1288816 Posted from - http://www.officehelp.in |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating a worksheet for every weekday
typo in cleaning up your code:
Application.DisplayAlerts = False Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Delete Application.DisplayAlerts = True -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Application.DisplayAlerts = False Sheets(Array("Sheet1", "Sheet2", "Sheet3").Delete Application.DisplayAlerts = True -- Regards, Tom Ogilvy "hungledink" wrote in message ... Thanks for that, it works well. The only thing is it leaves the first three worksheets, Sheet1, Sheet2 and Sheet3 in the workbook. I have tried to add some code so they are deleted but it keeps asking me to confirm the delete. Is there a way to stop this confirmation box appearing? The code I have entered is simply been produced by recording the deletion of the three sheets. -Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select Sheets("Sheet3").Activate ActiveWindow.SelectedSheets.Delete- -- hungledink ------------------------------------------------------------------------ hungledink's Profile: http://www.officehelp.in/member.php?userid=5563 View this thread: http://www.officehelp.in/showthread.php?t=1288816 Posted from - http://www.officehelp.in |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating a worksheet for every weekday
This works well thanks a lot Peter T;4044992 Wrote: You can temporarily change Application.DisplayAlerts = False / True. This revised routine avoids deleting any sheets and leaves th 'ordered' sheet codenames intact. Sub Test3() Dim dFrom As Date, dTo As Date Dim d As Date, i As Long dFrom = CDate("1/Jan/07") dTo = CDate("31/Dec/07") dTo = dFrom + 30 ' limit for testing Workbooks.Add n = 0 For d = dFrom To dTo If Weekday(CDate(d), 2) < 6 Then n = n + 1 If Worksheets.Count < n Then Worksheets.Add(after:=Worksheets(n - 1)).Name = Format(d, "yymmmd ddd") Else Worksheets(n).Name = Format(d, "yymmmdd ddd") End If End If Next Worksheets(1).Activate End Sub I amended the date format from last time but adjust to your needs. Regards, Peter T Thanks also to Tom, your help is appreciated -- hungledin ----------------------------------------------------------------------- hungledink's Profile: http://www.officehelp.in/member.php?userid=556 View this thread: http://www.officehelp.in/showthread.php?t=128881 Posted from - http://www.officehelp.i |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| WEEKDAY() function: display TEXT not numeric weekday | Excel Discussion (Misc queries) | |||
| Creating a Worksheet | Excel Discussion (Misc queries) | |||
| Creating or going to a worksheet | Excel Programming | |||
| Creating a new worksheet? | Excel Programming | |||
| Creating a new worksheet from another | Excel Programming | |||
Linear Mode
