Worksheet named from cell

AndyDuncombe

New member
Joined
May 4, 2016
Messages
24
Reaction score
0
Points
0
Excel Version(s)
365
Hi
So I'm trying to help out at work where we use Excel to roster our staff.
In a workbook we will have 52 tabs (worksheets) for 52 weeks. Is there a way I can name each different tab by calling from a cell - this might be from a list held in another tab?
I'm really sorry if this has been asked before!
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Andy,

This seems like a simple VBA naming exercise, but I am not sure what you mean by ... 'name each different tab by calling from a cell'. Can you calrify?
 

AndyDuncombe

New member
Joined
May 4, 2016
Messages
24
Reaction score
0
Points
0
Excel Version(s)
365
Andy,

This seems like a simple VBA naming exercise, but I am not sure what you mean by ... 'name each different tab by calling from a cell'. Can you calrify?

I've attached the spreadsheet. The worksheet "SHEET NAMES" and column B is the data source to name sheets 1 - 52. If there are format issues the column C is acceptable
 

Attachments

  • POTENTIAL OUTER ROSTER TEMPLATE.xlsm
    98.5 KB · Views: 14

NoS

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
This in a standard module should work
Code:
Sub NameThoseSheets()
    Dim ws As Worksheet
    Dim i As Long
    
For i = 1 To 52
    Set ws = Sheets("Sheet" & i)
    If Not ws Is Nothing Then
        With ws
            .Name = Sheets("SHEET NAMES").Cells(i, 2).Text
            .Cells(1) = Sheets("SHEET NAMES").Cells(i, 2).Value
        End With
    End If
Next i

End Sub
but my Excel 2010 wouldn't load your file without extensive repair, so who knows ???
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Sorry about the tardiness Andy, but try this

Code:
Public Sub NameThoseSheets()Dim ws As Worksheet
Dim i As Long
    
    For Each ws In ThisWorkbook.Worksheets
        
        If ws.Name Like "Sheet*" Then
        
            ws.Name = Worksheets("SHEET NAMES").Cells(Mid(ws.Name, 6, 2), "A").Value
        End If
    Next ws
    
    MsgBox "All done"
End Sub

PS Hope Pompey have a good second half :smile:
 
Last edited:

AndyDuncombe

New member
Joined
May 4, 2016
Messages
24
Reaction score
0
Points
0
Excel Version(s)
365
So do I copy and paste that verbatim into a view code module? Do I need to hit a functin key to make it update? If you were able to open my XLSM and can apply it to the first few sheets (weeks 1-3 for example) so I can see it working I'd be really grateful
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
It all depends upon your exact requirement Andy.

If you will need to do this sort of thing regularly and/or often, then you would be best to add some mechanism to launch the macro, such as an on-sheet button, or add a button to the ribbon.

If it is just a once a year thing, I would just run that macro on the workbook, and use that as a template for each new year.

If it is a once off just run that macro on the workbook.

Looking at your workbook though, I am guessing it needs quite a bit more, such as building the site roster for each week, catering for a 53 week year. How does the shift pattern get allocated?
 
Last edited:

AndyDuncombe

New member
Joined
May 4, 2016
Messages
24
Reaction score
0
Points
0
Excel Version(s)
365
The intention is to copy and paste the base roster into each 53 worksheets and amend manually
But I want an automated way of naming each sheet - hence callng from a list in the shift names worksheet where I can simply amend one cell which will have a global effect
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
I understand that Andy, but it doesn't really answer my questions.

If it were me, I would just add each new week as required, via code. I would have another routine to setup a new year. To facilitate this, I would have a default roster for each staff member, as you say, you can amend as required.

If you can give me a few days, I can knock-up an example of what I am thinking for you to look at.
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
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.
 

Attachments

  • AD Outer Roster 2022.xlsm
    45.5 KB · Views: 1
Last edited:

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
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.
 

Attachments

  • AD Outer Roster 2022.xlsm
    48.1 KB · Views: 2

AndyDuncombe

New member
Joined
May 4, 2016
Messages
24
Reaction score
0
Points
0
Excel Version(s)
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?
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
The title does reflect that, do you mean the sheet name? Your example had sheet names WEEK 1 and WEEK 2.
 

AndyDuncombe

New member
Joined
May 4, 2016
Messages
24
Reaction score
0
Points
0
Excel Version(s)
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
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
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.
 

Attachments

  • AD Outer Roster 2022.xlsm
    52.7 KB · Views: 2

AndyDuncombe

New member
Joined
May 4, 2016
Messages
24
Reaction score
0
Points
0
Excel Version(s)
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
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
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.?
 
Top