Thank you again for your reply!

Well Tom I do have more locations. again my fault ... there is another
location transport.

I too don't know why I get the 400 error. oh well.

I am researching VBA and am starting to recognize alittle in what you have
given me. but any further help in what you have written would be appreciated.

yours paul.

"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.