Hi,

I have a holiday chart that I have set up in excel...Along the top I have a row of cells (H2:JG2) which give the date. Along the left I have a column (B4:B28) showing the names of members of staff. The main bulk of the worksheet (H4:JG28) shows (using '1' for a full day holiday, and '2' as a half day holiday, and using conditional formatting to colour cells) who has what days off.

Hope that makes sense so far.

What I need to achieve is...to set up a macro that will ask for a name, then search the row to the right of that name (columns H to JG) for cells which contain values '1' or '2'. Then, for the ones it finds, copy values to a column on another sheet, and also, copy the date (from row 2) under which the value was found to the adjacent column.

Hope that makes sense too.

If anyone has anything like what I've mentioned above then I'd be grateful for the code, or if you're a whiz and can knock up code in a matter of seconds then please be a legend and feel free to do so. But I would be equally happy with any advice on which functions in VBA perform the operations I would require above, that way I can research them myself.

All the best things,

Tony