Each row of my worksheet has 240 cells. I would like to copy and paste certain cells to another worksheet if they contain data. There can be up to four guests listed in each row and I would like to have each guest listed separately in a new row on a different sheet but only with select data from the original worksheet. This sheet is updated daily and the list will continue to grow.

example: Cells A2:BH2 contain guest 1, BI2:DP2 contain guest 2, etc, etc. What I would like to happen is for Cells A2, B2, G2, K2, M2 to copy and paste into a new worksheet. Then look at BI2, if this cell is not blank then copy and paste some of the cells in that guests range into the next available row of the new worksheet. If the range of cells fro row 2 for guest 3 is blank then look at row 3 and do this same thing again.

Thank you in advance for any help with this. I have tried and failed to get a macro to work.