How do I split up data and copy it to a new workbook?

[bananajelly]

I have a table with the Columns: Date, Col1, Col2, Col3.

I want to copy all the rows with the same date into a new workbook. The date is in order. I have a code written that works. However, it takes forever to run, because it is looping through each row. I have A LOT of rows.

Is there no way to just select and copy a chunk and then paste it all just once? I think it takes long because it is pasting each individual row separately.

Thanks.

Here is my code:

Please Login or Register  to view this content.

[xladept]

Hi Nanner,

Try:

Please Login or Register  to view this content.

[bananajelly]

xladept, the code you wrote only copied/pasted one row. But when I changed the Range("A2") to Range("A2:A400"), it worked fine. However, I need something where I can add a changing variable.


For example, if n = total rows = 400, I have tried

Range("A2:A" & n &) and
Range(Cells(2,1), Cells(400, 1))

But I get an error for both.

[xladept]

Try:

Please Login or Register  to view this content.

That code has worked on other jobs???