Loop through rows in an excel worksheet and copy a range if the last cell isn't blank

I have looked and have not been able to find an efficient solution for what I am trying to do. I found the following code but I have to repeat the code as I have a cell that is not blank. I need to copy range(A2:O2) to another sheet and repeat this row being copied (looped) for every cell that has data starting in P2 and going out "n" number of cells in the same row. Each time the row is copied to the next blank row on the new sheet, I need the next number (starting in column P and going out "N" times) copied into column P, in the new row. I can send a spreadsheet as an example, but I don't know how to put it in this question. Please help. Here is the code:

Please Login or Register  to view this content.