Thank you again for your reply.

Yes I do have more locations... another called transport.

I am madly trying to learn vba and must say am quite thick as have not used
this part of brain for a long time... ask me to put someone on a bed pan or
control someones pain and things to do with patients then that side of the
brain is super sharp...

So Tom if you have the patience can you explain the code you have written so
I can apply it to my future learning.

Thank you

"Tom Ogilvy" wrote:

The 400 error has nothing to do with my macro. Try closing exel and starting
over.

You showed two locations in your data Inside and l5

It does all the days as written.

--
Regards,
Tom Ogilvy

"Paul" wrote in message
...
thank you so much for your fast reply. I tried your suggestion however it
returned the error 400?

when you say "you only showed 2 locations....." what does that mean?

I would like to print a sheet for each day with the title "recovery
staffing
day: date:" . preferrably the night before. In the example I only
included
mon (sorry was so tired could not type the rest) so I need a sheet for
tue,
wed, etc. I was hoping to put a button on the sheet to press to do this
function.

Thank you again for your quick reply and I hope you write soon. Paul

"Tom Ogilvy" wrote:

You only showed 2 locations, so this assumes two locations.

Sub MakeSchedules()
Dim sh As Worksheet, sh1 As Worksheet
Set sh = ActiveSheet
If LCase(sh.Cells(1, 1).Value) < "name" Then
MsgBox "Schedule isn't the active sheet"
Exit Sub
End If
Set rng = sh.Range(sh.Cells(2, 1), sh.Cells(2, 1).End(xlDown))
For i = 3 To 11 Step 2
Worksheets.Add after:=Worksheets(Worksheets.Count)
Set sh1 = ActiveSheet
sh1.Name = sh.Cells(1, i)
sh1.Cells(1, 1) = sh.Cells(1, i)
sh1.Cells(2, 1) = "Inside"
rw1 = 3
rw2 = rw1 + rng.Count + 1
sh1.Cells(rw2, 1) = "l5"
rw2 = rw2 + 1
For Each cell In rng
If LCase(cell.Offset(0, i)) = "inside" Then
sh1.Cells(rw1, 1) = cell.Offset(0, i - 1).Value
sh1.Cells(rw1, 1).NumberFormat = "hh:mm"
sh1.Cells(rw1, 2).Value = cell
rw1 = rw1 + 1
ElseIf LCase(cell.Offset(0, i)) = "l5" Then
sh1.Cells(rw2, 1) = cell.Offset(0, i - 1).Value
sh1.Cells(rw2, 1).NumberFormat = "hh:mm"
sh1.Cells(rw2, 2).Value = cell
rw2 = rw2 + 1
End If
Next
Set rng2 = sh1.Cells(rw1, 1).End(xlDown)(0)
sh1.Range(sh1.Cells(rw1 + 1, 1), rng2).EntireRow.Delete
Next
End Sub

--
Regards,
Tom Ogilvy


"Paul" wrote:

Hi all
I am making a roster at work and need to print out daily sheets from
the
master 4 weekly roster.

master roster (2 days, jack and aimee do not work tue so cells are
blank
A B C D E F
name hrs mon locn tue
anna 60 7:30 inside 8:30 l5
jess 50 13:30 l5 9:00 inside
terri 12 12:00 l5 7:30 l5
jack 8 7:30 inside
aimee 8 13:30 l5

New Worksheet
MON
INSIDE
7:30 anna
7:30 jack

l5
12:00 terri
13:30 aimee
13:30 jess

I am such a beginner at excel so any help is greatly appreciated.