[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

Creating a worksheet for every weekday

hungledink

12/17/2006 5:07:00 PM


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.off.../member.php?u...
View this thread: http://www.off.../showthread.php...

Posted from - http://www.off...
6 Answers

Peter T

12/17/2006 7:29:00 PM

0

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" <hungledink.2iz1rf@NoSpamPleaze.com> wrote in message
news:hungledink.2iz1rf@NoSpamPleaze.com...
>
> 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.off.../member.php?u...
> View this thread: http://www.off.../showthread.php...
>
> Posted from - http://www.off...
>

hungledink

12/17/2006 8:12:00 PM

0


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.off.../member.php?u...
View this thread: http://www.off.../showthread.php...

Posted from - http://www.off...

Tom Ogilvy

12/17/2006 9:26:00 PM

0

Application.DisplayAlerts = False
Sheets(Array("Sheet1", "Sheet2", "Sheet3").Delete
Application.DisplayAlerts = True

--
Regards,
Tom Ogilvy


"hungledink" <hungledink.2iza2l@NoSpamPleaze.com> wrote in message
news:hungledink.2iza2l@NoSpamPleaze.com...
>
> 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.off.../member.php?u...
> View this thread: http://www.off.../showthread.php...
>
> Posted from - http://www.off...
>


Peter T

12/17/2006 9:39:00 PM

0

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" <hungledink.2iza2l@NoSpamPleaze.com> wrote in message
news:hungledink.2iza2l@NoSpamPleaze.com...
>
> 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.off.../member.php?u...
> View this thread: http://www.off.../showthread.php...
>
> Posted from - http://www.off...
>

Tom Ogilvy

12/17/2006 9:42:00 PM

0

typo in cleaning up your code:

Application.DisplayAlerts = False
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Delete
Application.DisplayAlerts = True

--
Regards,
Tom Ogilvy



"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:%233WvAJiIHHA.420@TK2MSFTNGP06.phx.gbl...
> Application.DisplayAlerts = False
> Sheets(Array("Sheet1", "Sheet2", "Sheet3").Delete
> Application.DisplayAlerts = True
>
> --
> Regards,
> Tom Ogilvy
>
>
> "hungledink" <hungledink.2iza2l@NoSpamPleaze.com> wrote in message
> news:hungledink.2iza2l@NoSpamPleaze.com...
>>
>> 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.off.../member.php?u...
>> View this thread: http://www.off.../showthread.php...
>>
>> Posted from - http://www.off...
>>
>
>


hungledink

12/17/2006 10:17:00 PM

0


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 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
>
>

Thanks also to Tom, your help is appreciated.


--
hungledink
------------------------------------------------------------------------
hungledink's Profile: http://www.off.../member.php?u...
View this thread: http://www.off.../showthread.php...

Posted from - http://www.off...