Page 2 of 3 FirstFirst 1 2 3 LastLast
Results 11 to 20 of 24

Thread: Worksheet named from cell

  1. #11
    Acolyte AndyDuncombe's Avatar
    Join Date
    May 2016
    Posts
    24
    Articles
    0
    Excel Version
    365


    Register for a FREE account, and/
    or Log in to avoid these ads!

    appreciate this

  2. #12
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,938
    Articles
    0
    Excel Version
    O365
    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.
    Attached Files Attached Files
    Last edited by Bob Phillips; 2022-01-18 at 03:39 PM.

  3. #13
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,938
    Articles
    0
    Excel Version
    O365
    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.
    Attached Files Attached Files

  4. #14
    Acolyte AndyDuncombe's Avatar
    Join Date
    May 2016
    Posts
    24
    Articles
    0
    Excel Version
    365
    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?

  5. #15
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,938
    Articles
    0
    Excel Version
    O365
    The title does reflect that, do you mean the sheet name? Your example had sheet names WEEK 1 and WEEK 2.

  6. #16
    Acolyte AndyDuncombe's Avatar
    Join Date
    May 2016
    Posts
    24
    Articles
    0
    Excel Version
    365
    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

  7. #17
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,938
    Articles
    0
    Excel Version
    O365
    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.
    Attached Files Attached Files

  8. #18
    Acolyte AndyDuncombe's Avatar
    Join Date
    May 2016
    Posts
    24
    Articles
    0
    Excel Version
    365
    this is truly incredible! In an ideal worls I'd like to use data validation to populate the names that show in column A

  9. #19
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,938
    Articles
    0
    Excel Version
    O365
    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.?

  10. #20
    Acolyte AndyDuncombe's Avatar
    Join Date
    May 2016
    Posts
    24
    Articles
    0
    Excel Version
    365
    100% correct

Page 2 of 3 FirstFirst 1 2 3 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •