If data entered in cell, on same row, copy and paste data from other cells to front sheet

Hi Guys, I'm back once more, hoping you can continue my salvation.

I've got raw data from a rostering program. I want to create a summary page of all employee's overtime hours and the shift date, commence time and end time.

Overtime hours are in column O. If cell empty, skip to next cell. If cell has data (any data, doesn't matter what), then move to column D on same row and paste data to dashboard!D5, then move to column G, paste data to dashboard!E5, then loop onwards to the next cell in column O with data.

It would probably be best if a 'insert row' was used just prior to the pasting of data on the dashbaord, as the spreadsheet will be run monthly, and the dashboard will accumulate new data each time, which, without an insert row feature, will eventually overun previous rows.

sample workbook attached

Sample workbook.xlsm

you guys are unbelievably awesome

Thanks in advance!

It is not clear if you want to copy in to next row or column.

Hi AB33,

I apologise, I realise that by providing only one page in my sample workbook I've made it confusing about what I'm attempting to do.

I have attached a new sample workbook with three pages and the summary page.

Your formula presented the data exactly how I wanted, thank you very, very much , but I'm not sure it will work for the repeated sheets/employees.

When I was talking about inserting rows, as you can now see in the new sample workbook, I need to insert new rows for the data to prevent it from over running employee's further down the sheet.

Thank you again for your amazing help ^_^


Sample workbook.xlsm

Christ,
Try the attached.

It works on the sample. It will definitely work if you run it once. it also work, if you re-run it and if the layout of the dashboard remains unchange.

You're an absolute wizard! I tried for days to make this work and just couldn't figure it out. I think I'm a little to new at VBA to be trying advanced stuff, but I'll keep trying! thanks again for your amazing help!

You're a champion!