Loop Copy Rows, Paste in next available row given a value in a cell

[Craddox]

Hi, This is my first time posting (many times reading).

I have a workbook at can have variable numbers of rows submitted (up to 200k) but copy and pasting formula in each possible cell in 200k rows is swamping my PC memory. The likelihood of 200k rows is very small (usually more like 10k) so killing the workbook for a slim eventuality seems pointless. I have set up a count of how many rows are "actually" submitted and need a VBA code to copy and paste a row of data (formula) against the count stated in a cell and to loop until the cell number has been met.

If anyone can help it would be much appreciated. I've been trying to stitch together pieces of code but not getting very far.

Many thanks

 

[Marc L]

​

Hi, when more than 65k rows the better is to avoid Excel and use a Database software …​

​

Did you try to use an Excel table ? (so no need to copy anything, all is automatic)​

 

[Craddox]

​

Hi, when more than 65k rows the better is to avoid Excel and use a Database software …​

​

Did you try to use an Excel table ? (so no need to copy anything, all is automatic)​

Hi Marc,

Access is my next stop but the coding is critical so trying to generate in excel initially before paying someone to migrate it to SQL with an access front end plus we need something working initially as a stopgap. I've had bad experiences of pivot tables in excel (not copying things etc) so I try to keep away from them especially when there's a lot of data as it's impossible to sanity check them. I'll take another look at pivot tables as an interim solution unless anyone con come up with some VBA code. Thanks