Macro Help

[james620]

I have a file (attached excel 2003 is what i use at work) which I am trying to do a couple of things with.

1stly I have written a macro (my first!!) to look down each colummn for a term, shown as 1-12 on my sheet. When it finds the term it goes across to the surname and places it in the corresponding post box at the top (still with me?) it then checks against a list of names within the macro to check their drivers or not and then colours them, so i know who are and who arent. it then colours any blanks with a blue so i know i have them

can anyone see how i could simplify this? and instead of it having to check each name individually in the list i have place 2 lists in a spare sheet which i then was it to check the top section against and colour silver(or yellow as it is now) if they are current, and stay white if they are expired, or if its a blank colour blue!





also id like to, to make life easier, copy across data from test 2 into test sheet. test 2 is a shift for the year, which i get, i then have to type them all in for the year. i've had to split the first sheet up into 2 sections as its too many days for 1 sheet by the looks of it.

Id like to be able to get all the data from the shift spreadsheet to transfer over into 1 row, from the 52 the data is displayed in?

im not that good at these macros yet, but i thank you for looking and for your time !!!

[james620]

second file

[Bob Phillips]

It's password protected!

[james620]

b**ger! here it is unpassworded !!

[Bob Phillips]

Is this what you mean by simplify

[james620]

thats perfect thank you!so much

im being a bit cheeky but do you have any ideas on the second question?

[Bob Phillips]

I baled out on that one as I didn't really understand it.

[james620]

the second sheet is a shift pattern which has a row a week, ie 52 rows, i want that to be converted into one long row which replicates into the first sheet, i will have 1 shift pattern for each person which will be called by thier initials ... ie IG, GT, MP, I want the macro to look at gibson say that sheet will be IG (i will have a hidden column with the initals which will match the sheet for the shift pattern). when it runs it will look for IG' shift pattern and enter it for the year,

it just saves me having to sit there and type them out for 100 people

[Bob Phillips]

I can understand the why, I just still don't understand the what. Sorry.

I THINK you are saying take sheet1 and sheet2 and create separate sheets per resource, but I see no data on those sheets and where do these initials come from?