Staff rota help

[flier109]

I have data which looks a bit like this, for every day of the week over a period of 6 weeks. We have a bank of around 30 relief staff, so some may not work for weeks and others work most days. The abbreviations are the type of work they are rota'd to do, which are for different rates of pay and shift times. TL=Team Leader. TW=Team Worker, E=Escort etc
This data feeds into employee time sheets which are then printed off for them to correct or adjust when they work.
I need to create a summary which shows just those employees who are working each day, printed in one page. This needs to be as simple to do as possible, as it will be done by a staff member not particularly confident in excel. Ideally it would be in the order of TL, E, TW. Any ideas, anyone? Would it have to be a macro?

Employee Name Week 1 Monday Week 1 Tue etc Week 2 Monday Week 4 Wednesday

mr hobbs	TL	TW	E
Miss hobbs	TW	TW		Hol
Mr X	TW			TL
Miss X	E	TW	TW

 

[My Aswer Is This]

This image shows names but does no show what column name is in.
So I assume name is in column A
Now it appears as if Mr Hobbs only worked Mon Tues and Wed
From looking at this no one here worked the whole week
Assuming a whole week means Mon -Friday
And you said only print sheet for those who worked entire week
So I'm looking to see who worked for 5 days Mon-Friday
Is this correct?
Or please explain.

 

[flier109]

This image shows names but does no show what column name is in.
So I assume name is in column A
Now it appears as if Mr Hobbs only worked Mon Tues and Wed
From looking at this no one here worked the whole week
Assuming a whole week means Mon -Friday
And you said only print sheet for those who worked entire week
So I'm looking to see who worked for 5 days Mon-Friday
Is this correct?
Or please explain.

The format didn't post well. There is a column for each day of the week (Monday to Saturday).

What I'd like to see is who is Rota'd to work each day in the order of who is TL, who is TW, who is E. I only want to print who is working in those roles for the month. Hope that makes things a little clearer.

 

[My Aswer Is This]

See I need specific details.
You said:

There is a column for each day of the week (Monday to Saturday).​

But you did not says what columns
Is this column B To H

And what is this word:

Rota'd to work
What is Rota​

Are you saying who has data in Columns B To H
So if their is something in those cells copy that row to a new sheet
Then you can print that sheet

 

[flier109]

First column contains the staff name, thend each subsequent column contains the day of the week (for 6 weeks). Rota is the staff rota, ie when they are due to work.

So, yes, I agree, I would want to query who has data in columns B onwards, so I'm starting to see a way forward, but any advice would be appreciated.

 

[My Aswer Is This]

So we are looking to see who has some value in column B to H
Because you said each row has 6 weeks of data.
So are we looking to see who has data for just one week or the entire 6 weeks.
And some value means any value correct?

 

[flier109]

it would be the entire 6 weeks, and yes, any value would be correct

 

[My Aswer Is This]

Try this:
Starts in Row(2)
Looks in each row from column B to Column AK
If all cells have some value row is copied to sheet(2)

Sub CommandButton1_Click()
'Modified  1/9/2020  8:11:02 AM  EST
Application.ScreenUpdating = False
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Dim Lastrowa As Long
Lastrowa = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row + 1
Dim ans As Long
    For i = 2 To Lastrow
        ans = Application.WorksheetFunction.CountA(Cells(i, 2).Resize(, 36))
            If ans = 36 Then Rows(i).Copy Sheets(2).Rows(Lastrowa): Lastrowa = Lastrowa + 1
    Next
Application.ScreenUpdating = True
End Sub

 

[flier109]

Try this:
Starts in Row(2)
Looks in each row from column B to Column AK
If all cells have some value row is copied to sheet(2)

Sub CommandButton1_Click()
'Modified  1/9/2020  8:11:02 AM  EST
Application.ScreenUpdating = False
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Dim Lastrowa As Long
Lastrowa = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row + 1
Dim ans As Long
    For i = 2 To Lastrow
        ans = Application.WorksheetFunction.CountA(Cells(i, 2).Resize(, 36))
            If ans = 36 Then Rows(i).Copy Sheets(2).Rows(Lastrowa): Lastrowa = Lastrowa + 1
    Next
Application.ScreenUpdating = True
End Sub

Thank you for taking the time to help me with this. I can't get it working just now but will get another look later on today

 

[My Aswer Is This]

You said 6 weeks of Monday to Saturday

Not sure if that meant also Saturday of Mon through Friday

So just make it simple for me. Do workers work 5 days a week or 6