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.

Quote from Carim

Hello,

Attached is your test file

Hope this will help

Display More

Wow, that was so quick, Thanks a ton! But it seems to work only up to the number 26, after which the copy doesn't happen.

Edit: I found the solution is to copy the formula beyond row 25, thanks a ton!

Quote from Carim

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.

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.

Quote from Carim

In my humble opinion ... all tedious tasks ... should be sub-contracted to a macro ...

Truer words weren't spoken

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.

Quote from Carim

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.

Quote from Carim

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

Display More

Works just perfect! Thank you so much!!!