Worksheet named from cell

[AndyDuncombe]

appreciate this

[Bob Phillips]

Here is a first cut Andy.

A few points to note:
- whenever you need a new week roster sheet, just click the 'New Roster' button on the BASE sheet - I have left a first week sheet in the workbook, the macro will automatically create the new roster sheet. You can click this as many times as you want, but I would just add as any/few as you actually need at the time, you may want to add a button that creates 4 say
- origin ally I was going to hold a default daily shift pattern for each staff member and build the roster from that, but in the end I decided to use your base template, it has everything needed
- you shouldn't need to change some things on the 'App Settings' sheet, the dates are all maintained automatically, as are the periods, and the Site tables lines (the number of people working at that site)
- for a new year, just click the 'New Year' button on the BASE sheet, it removes all the WEEK n sheets, updates the dates, and saves the new file
- you can rename the file but don't change the year suffix, the code uses that when it creates a New Year workbook.

My original thought was to provide a ribbon to launch the code, it is nice because you can group items and brand it, but there were too few functions to warrant it IMO, maybe later if you extend it.

See what you think.

[Bob Phillips]

Found a small bug in the code, so fixed version attached.

I also changed the new worksheets adding to be latest first, after App Settings, makes for better navigating when there are many week worksheets.

[AndyDuncombe]

Bob many thanks - this looks great!

However I want the title of week 1 sheet to reflect (App settings) week 1 date AND week 2 to reflect (App settings) week 2 date etc?

[Bob Phillips]

The title does reflect that, do you mean the sheet name? Your example had sheet names WEEK 1 and WEEK 2.

[AndyDuncombe]

Which I'd like to rename as Sunday 2 Jan + sunday 9 Jan etc or whatever variant will fit. I think this would call from the app settings sheet data in column E. In 2023 I thn just need to change cell e2 to globally change all sheet names

[Bob Phillips]

I have made that change. I named the sheets as ddd d mmm as it takes less space, is just as clear, and means you see more roster sheets at one time. Each time a new roster sheet is added, the date in the list of sheet names table in 'App Setting' is given a hyperlink to that sheet, so it acts as a simple ToC.

I also changed the paydates table so that calculates automatically, save you doing it when you go into a new year.

[AndyDuncombe]

this is truly incredible! In an ideal worls I'd like to use data validation to populate the names that show in column A

[Bob Phillips]

That is certainly doable, we have a list of names.

I assume you would want SRM to only show the 4 staff designated at SRM, Basingstoke the 6, etc.?

[AndyDuncombe]

100% correct