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

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

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

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

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

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

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