+ Reply to Thread
Results 1 to 9 of 9

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

  1. #1
    Registered User
    Join Date
    02-12-2019
    Location
    Cleveland, Ohio
    MS-Off Ver
    2016
    Posts
    4

    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.
    Attached Files Attached Files

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    9,221

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

    Hello & Welcome to the Forum,

    Give this a try on a copy of your workbook...

    Please Login or Register  to view this content.
    HTH
    Regards, Jeff

  3. #3
    Registered User
    Join Date
    02-12-2019
    Location
    Cleveland, Ohio
    MS-Off Ver
    2016
    Posts
    4

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

    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?

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    9,221

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

    This is copying for column 1 (column A) to column 9 (column I)

    Please Login or Register  to view this content.
    For columns A thru V, that would now be...

    Please Login or Register  to view this content.
    If the samples are now in W, X, and Y, you would adjust...

    Please Login or Register  to view this content.
    to

    Please Login or Register  to view this content.
    Typically, if your adjusting columns you are going to have to adjust your variables and that should be good enough
    Last edited by jeffreybrown; 02-12-2019 at 03:52 PM.

  5. #5
    Registered User
    Join Date
    02-12-2019
    Location
    Cleveland, Ohio
    MS-Off Ver
    2016
    Posts
    4

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

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

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    9,221

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

    You are very welcome and thanks for the feedback.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  7. #7
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    Posts
    2,646

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

    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.

  8. #8
    Registered User
    Join Date
    02-12-2019
    Location
    Cleveland, Ohio
    MS-Off Ver
    2016
    Posts
    4

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

    Thank you both!

  9. #9
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    Posts
    2,646

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

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1