Autoformat date as sheet names

PP

Board Regular
Joined
Sep 30, 2006
Messages
139
How would one go about doing this?

First sheet's date is inputted / renamed manually, eg (2.10.06) - UK format. Sheet dates start on Monday and go through to Friday - Saturday & Sunday dates are skipped. Macro autodates all sheets in workbook using first sheet as the 'start date'.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Joined
Jul 30, 2006
Messages
3,656
Are there always five sheets in the workbook?

What are the default sheet names?

Is this workbook a template file with five sheets?


Have a great day,
Stan
 

PP

Board Regular
Joined
Sep 30, 2006
Messages
139
Are there always five sheets in the workbook?

No - the number of sheets in the workbook can vary considerably depending on the length of the term. Sometimes it can have 20 sheets, other times it might have 40.

What are the default sheet names?

Sheet 1, sheet 2 etc. Except that sheet 1 would be renamed to the date of the first day of term.

Is this workbook a template file with five sheets?
No - see first answer.


Have a great day,
Stan

You too!
:P
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

does this help?
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
 

PP

Board Regular
Joined
Sep 30, 2006
Messages
139
does this help?
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

Hi,

There are a few problems with this but many thanks for the input. 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. How about a Pop-up box that asks for date ranges? 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:

Title of input box: Input School Half Term Date Ranges

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

Would the user have to also input the start day for each half term or would excel 'know that 1.10.06 is a Sunday?


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

Oh by the way it is my birthday today!
:eek:
 

Forum statistics

Threads
1,136,262
Messages
5,674,708
Members
419,520
Latest member
Jennifer4Dillon

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