Roster with each person getting their own weekly sheet printed [Closed]

Report
Posts
5
Registration date
Sunday January 20, 2013
Status
Member
Last seen
January 22, 2013
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

Ive put together a spreadsheet for work, where we have a main page called roster, the whole weekly roster for all staff members goes onto that sheet. We have about 30 staff.

Ive made every staff member their own weekly sheet which has only their own roster automatically copied from the main roster sheet.

How ive done this so far is just using simple =Roster!B3 , =Roster!B4 , =Roster!B5 etc
So monday am on dave's weekly sheet looks at cell b3 on the main roster sheet tuesday looks at b4 , etc

It is working but we keep getting glitches and mistakes. The boss actually does the roster and he isnt that computer savvy so i need this to just work with no errors or glitches.

Im just looking for an easier more reliable way to do this. If the roster comes out with a mistake on one of the staff members weekly sheets because something went wrong somewhere ,, they might not turn up and the proverbial hits the fan.

Any advice would be appreciated

Dan

5 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
767
are you looking to create new rosters or copy the data back to main roster. Either way i think you would be serve best by use of macro
Posts
5
Registration date
Sunday January 20, 2013
Status
Member
Last seen
January 22, 2013

Im looking for each staff members weekly roster to be copied from the main roster sheet, which is huge, into their own personal sheet.

Each staff members weekly sheet will then be printed and handed out.

Last week for example, Jon's weekly sheet came out with someone else's shifts on it. I dont know how, i tested everybody's weekly sheet before i gave it to the boss to make sure everybody's weekly sheet was getting the info from the right section of the roster. But somehow, after the boss was finished, it was wrong.

If the boss cut's n paste's into the roster, those entries wont show up on the weekly sheets, they just show up as #ref. If he copy's n paste's its all fine.

Glitches and errors just keep happening. Im not sure how to stop it. So im looking for another way.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
767
macro would be best way
you can generate a good macro template by starting macro recorder, creating one sample roster for an employee and then stopping the macro.
Posts
5
Registration date
Sunday January 20, 2013
Status
Member
Last seen
January 22, 2013

Each persons weekly sheet would need to get the information from a different section of the main roster sheet though.

How would you get around that ?
Posts
5
Registration date
Sunday January 20, 2013
Status
Member
Last seen
January 22, 2013

A separate macro for each person ?
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
767
no one macro for all.
Posts
5
Registration date
Sunday January 20, 2013
Status
Member
Last seen
January 22, 2013

Start the macro recorder

Copy person 1's info from the roster sheet and paste into person a's weekly sheet.

copy person 2's info from the roster sheet and paste into person 2's weekly sheet

etc etc.

then just run the macro after the roster has been updated.

Is that the way it would work ?
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
767
no, the macro you will get from the recorder is to give you a very good template to start work from. that macro then needs to tweaked a bit to make it usable for any and all employee
so
1. start mavro
2.Copy person 1's info from the roster sheet
3 paste into person a's weekly sheet.
4 stop macro

this will give you a general template that can be update to be used for any person

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!