No Macro to copy values to specific number of rows

  • I need code to copy values from row 1 of a sheet down to a specified number of rows. Please see sample attached. I need the data I enter from A2 to E2 to be copied 24 times (based on the value I enter in F2). The total count should include row 1, so basically it should be copied 23 times.

    I don't know if there is a way to do this in Excel without a macro, any help would be appreciated! TIA.

  • Carim

    Changed the title of the thread from “Macro to copy values to specific number of rows” to “No Macro to copy values to specific number of rows”.
  • Glad this could help ...


    You only need to copy row 2 down to the max row you do plan to use ...;)

    One more small thing, after the rows are copied I need to copy paste them into another workbook, when I do a (Ctrl+A) it selects even the rows that are blank because they have a formula in them. Since the number in F2 keeps changing, I need to keep copying the formulae to the specified row, instead of which I can just (Ctrl+D) the data to the specified number of rows.

  • Are you now looking for a keyboard shortcut to select non-blank cells ...?


    To my knowledge, there is none ... but you could design your own ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Sometimes I need to copy data to more than 600 rows, so I earlier used to do a (Ctrl+D) for data and then (Ctrl+A) and copy paste into my main workbook. It gets tedious to hold shift and page down to exactly 600 rows. So was looking for a way to do it faster by automatically copying the data to specified rows and then copying it into my main workbook. Thanks again for your time on this.

  • I have a big list of employees running into thousands, I need to allot them shifts, dates and time. Some batches are 600, some are 480 and so on. So I enter the Batch 1 details in row 1 and then do a (Ctrl+D) to the specified number of rows. I then copy this and paste it beside the employee details in my main workbook, the employees are then sent their respective detail via mail merge. I repeat the process for batch 2 and so forth. Attached sample file which shows different batch details.

  • So you are dealing with a certain number x of batches ... and each batch has a specific number of counts y ...


    What is not very clear is why would you need to copy, say batch #1 for its respective number of counts ... before copying this intermediate result to its final destination ...


    Is there something preventing you from going straight from the Source ... directly to the Destination ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • So you are dealing with a certain number x of batches ... and each batch has a specific number of counts y ...


    What is not very clear is why would you need to copy, say batch #1 for its respective number of counts ... before copying this intermediate result to its final destination ...


    Is there something preventing you from going straight from the Source ... directly to the Destination ...?

    I'm just more confident to do it on a separate sheet because the main file is also used by others. However, I do sometimes enter it directly onto the file when the numbers are small and my work is quick, but since the employee data there is huge, it is faster to do the data drag in a different file and then just copy paste. Another reason I do this is because there are times when all details are the same except the date, so I just change the date and I have a dataset ready within one step for the next batch.

  • To make your life slightly easier ... and take into account your wishes :


    With a combination of " no-macro to copy " - " formula to copy " - " shortcut to select cells" ....


    see attached test file ...


    Hope this will help

    :)

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)