Macro to copy/insert row X times based on a value in a cell

I am working with a worksheet that has tens of thousands of rows already, but I need to copy each line based on a value in a cell. I have attached an example spreadsheet that has the source data and then the final format needed.

The data is based on samples that I have collected from patients in a research project. I collected blood and then spun the blood down to separate the Serum, Plasma, and Buffy Coat. The data is the exact same for all samples regardless of Sample Type, but the database output had just one row and then the number of Serum/Plasma/Buffy Coat instead of giving me a separate line for each individual sample. I need to take the data for Row 1 on the "Source Data" tab and copy it 3 times since I have 3 Serums (cell J2), 2 more times for Plasma (cell K2), and 1 more time for Buffy Coat (cell L2).

Finally, it would be great if it could also enter the column header for each of those (J1, K1, or L1) in a new "Sample Type" column (column J on the "Results" tab) so I would know if the samples were Serum/Plasma/Buffy Coat.

Another way for me to do this is to have one macro where I just change the cell value in the macro text that the macro looks at to copy lines and look for header. So, I could tell the macro to look at J2 and run the Macro to get all the lines for Serum. Then edit the macro to look at K2 for Plasma, etc.

Thanks in advance and let me know if you need additional information.

Hi Jeff,

This is fantastic! One more question for you if I can. If may data has additional columns (say data is columns A through V) and then the 3 columns with the number of samples are now in W, X, and Y how does that change the macro text (I would need the data for A-V copied)? I guess my basic question is if the number of columns with data varies, are there simple/consistent areas of the macro text I can change to make it work?

Thank you so much!! This will save me tons of hours.

I had this ready but Jeff was way ahead of me.
Changed it so you don't have to adjust the code for more Columns as long as the last 3 are always the Serum/Plasma/Buffy Coat Columns.
In other words, works with any amount of Columns.
It assumes you have a Sheet named "Results" (without quotation marks) as per attached in your first Post with the same Headers as in "Source Data" Sheet.
It will clear all tha Data, except the first Row, in that Sheet first.

Please Login or Register  to view this content.

Thank you both!

In case you're thinking about using the code from Post #7, you have to change this line

Please Login or Register  to view this content.

to this

Please Login or Register  to view this content.

You will not see a difference but it was a mistake none the less.