copy paste row that match criteria

Hi, attached workbook calculates serial dilutions in a consecutive sequence (1:100 > 1:200, 1:200 > 1:300, 1:300 > 1:400 etc). I'm having problem when I want to reuse a previous concentration to do the dilution (1:100 > 1:200, 1:200 > 1:300, 1:100 > 1:400). The "execute" button is supposed to copy the row with the dilution that matches P24 to the last row. As you can see on the worksheet, the actual number (1000) was copied instead of the face value of 500. On top of that, the entire row that matches criteria gets copied instead of the range A:K. Can someone please help me with this?

Hi xladept, thank you so much for helping. I tried your code and it works to a certain extent. Now it copies only the range that I want, but not the face value of the cell. It still copies 1000 instead of 500. Hope you can help with that

Thank you. Another question, Is it possible to keep the formula in the cells while doing so? after the macro copy and paste, the formula in the cell is gone and it is unable to calculate for the rows below

hmmm ok, so it's not possible even if you were to write my formulas into vba?

Lets try that! So the formulas code it into column J? and when the copy and paste happens, cells in column I will get the value from column J? is that what you are suggesting?