Format workbook by autoadding a range of dates as sheets

Status
Not open for further replies.

PP

Board Regular
Joined
Sep 30, 2006
Messages
139
The code underneath was kindly suggested by Jindon for formatting worksheet names as dates.

Code:

Sub test()
Dim ws As Worksheet, n As Integer
For Each ws In Sheets
Do Until WeekDay(Date + n) <> 1 Or WeekDay(Date + n) <> 7
n = n + 1
Loop
ws.Name = Format(Date + n,"dd.mm.yy") : n = n + 1
Next
End Sub


However, there were a few problems with this but many thanks for the input Jindon. The code above always starts with the current date rather than a user specified date which means that the sheet must be prepared 'on the day' for it to label the sheets accurately. Often it is very hectic on the first day so it would be beneficial if the worksheet can be prepared in advance. Another problem is that it also includes dates that fall on Saturday & Sunday - as we never work weekends these sheets will never be used.

Maybe a better design idea on my part is needed. Is it possible to make a macro that creates a Pop-up box that asks for date ranges and generates copies of the first worksheet labelled as dates? There are ALWAYS 6 half terms to a school year so how about a 6x2 pop up box / table that is formatted in the following way (remembering that saturdays and sundays must be excluded):

Title of input box: Input School Half Term Date Ranges

******** Start Date ** End Date **
Autumn 1 03.09.06 18.09.06
Autumn 2
Spring 1
Spring 2
Summer 1
Summer 2

The macro would then create a series of sheets from 03.09.06 to 18.10.06 as well as the other 5 date ranges (excluding weekends) using the first sheet as a template. Would the user have to also input the start day for each half term or would excel 'know that 04.09.06.06 is a Monday and 18.10.06 is a Wednesday?


If the macro could create worksheets for these date ranges and clone the first worksheet in the workbook then that would save me LOADS of time copying and pasting each year.

Oh by the way it is my birthday today!
Eek
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

PP

Board Regular
Joined
Sep 30, 2006
Messages
139
OK I am creating the autoform with the help of my dear friend mr. google. I will post to check that this form has been correctly inputted. I am guessing that it would be best to use drop down boxes for the dd-mm-yy and a command button called close to shut down the autoform and save the ranges? Would more experienced users agree with this?
 

PP

Board Regular
Joined
Sep 30, 2006
Messages
139
OK I am creating the autoform with the help of my dear friend mr. google. I will post to check that this form has been correctly inputted. If anyone can confirm that this autoform can be used to achieve the result stated above it would be appreciated.

Thanks.
 
Status
Not open for further replies.

Forum statistics

Threads
1,136,926
Messages
5,678,606
Members
419,775
Latest member
joh93

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top