Is it possible?? If it is then please tell me how.

[Guest]

I am trying to create a spreadsheet that is like a job rota. I would like to
be able to search a column (day) on one sheet and find a certain result
(job), when that result is true i would like to be able to move corresponding
information in the frst column (name) to another sheet to create a list of
all the names doing a selected job. Confused?? you bet I am.

Any help or ideas would be appreciated.

Monty

 

[Guest]

do you have two or three sheets?
you first want to know if a "Job" appears on one sheet
you then go to a second sheet and extract names
the list of names associated with the job then gets listed on what sheet?
on sheet 2 how are jobs and people formated?
job with a list of people next to it or under it, or
two columns (rows) one with jobs and one with people (each can occur many
times)

 

[Guest]

Hi BJ,

Firstly thanks for trying to help.

In answer to your questions there are multiple sheets, but all the
information is transfered to a sheet called "master rota" which is where i
would like to source the information from. It is then transfered to a daily
sheet eg "sunday listing".

The layout of the "master sheet" is each man in the team (Col A) then the
job is in (col B). Eg joe bloggs Rest Day

The sheet i am transfering to ("sunday listing") has job headings for each
column so lets say column a is rest day. The idea is to put anyone who has
"rest day" next to their name in the "master listing" worksheet into a new
list in the "sunday listing" sheet.

I hope thats what you're after, I think I may be trying to over complicate
things.

Monty

 

[Jef Gorbach]

Hi BJ,

Firstly thanks for trying to help.

In answer to your questions there are multiple sheets, but all the
information is transfered to a sheet called "master rota" which is where i
would like to source the information from. It is then transfered to a daily
sheet eg "sunday listing".

The layout of the "master sheet" is each man in the team (Col A) then the
job is in (col B). Eg joe bloggs Rest Day

The sheet i am transfering to ("sunday listing") has job headings for each
column so lets say column a is rest day. The idea is to put anyone who has
"rest day" next to their name in the "master listing" worksheet into a new
list in the "sunday listing" sheet.

I hope thats what you're after, I think I may be trying to over complicate
things.

Monty

This might help?
Copy MasterRota to a temp worksheet just in case something goes wrong
Add the neccessary daily sheets
Loop thru the data, moving each row to its corresponding daily sheet based
its column(b) value
Delete the temp worksheet


'first copy data to a temp worksheet just in case something goes wrong
Sheets("Master Rota").Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = "temp"

'add each daily sheet
Sheets.Add.Name = "Sunday"
Sheets.Add.Name = "Monday"
--etc--
'Title row each new sheet
For Each WS In Worksheets(Array("Sunday","Monday"))
WS.Range("A1:G1").Value = Sheets("temp").Range("A1:G1").Value 'copy title
row
-- add desire title formating here --
Next
'Move people based upon column(b)
Sheets("temp").Activate
finalrow = range("B65536").end(xlup).row 'change to your longest column to
catch everyone
For Each cell In Range("B1:B" & finalrow)
Select Case cell.Value
Case "Sunday": cell.EntireRow.Cut
Sheets("Sunday").Range("A65536").End(xlUp).Offset(1, 0)
Case "Monday": cell.EntireRow.Cut
Sheets("Monday").Range("A65536").End(xlUp).Offset(1, 0)
--etc--
End Select
Next
'sheets("temp") is likely now empty so you may want to remove it without
bothering user
Application.DisplayAlerts = False
Sheets("temp").Delete
Application.DisplayAlerts = True
end sub