Need vba-copy data from second row till last row & paste in another workbook after lastrow

[Richavlaues]

Hi All,

Can I get vba code to copy data from excel starting from 2nd row (ie., to exclude headers) and paste the data in another workbook that already contains few rows of data. The tricky part is to paste the copied data after lastrow of destination workbook.

Ex:
1. I have mock_data workbook and that's the source data.
2. In real time I will be having more than 100 columns but for example, I have included only few.
3. Copy data from 2nd row till last row (but column range is dynamic. Its not fixed to 10 columns or 20 columns)
4. Paste copied data to destination workbook towards end of row ie., lastrow + 1

I really appreciate any help provided.

Thanks,
Richa

[daffodil11]

Something like this can do it. Just change filepath.

Please Login or Register  to view this content.

[Richavlaues]

Hi,
Thanks for quick reply. I really appreciate Your help.
In meantime, I tried to record the macro and below code was generated:

Please Login or Register  to view this content.

Can You please let me know if there's a way to use below line of code and modify it to suit my requirements?

Please Login or Register  to view this content.

I tried this approach as it does not consider column index or labels as reference. Say for example: .Range("A2:a100") and .Range("O" & unicorn.Row)).Copy

Thanks,
Richa

[daffodil11]

Yeah, you could skip the whole loop and do it all at once:

Please Login or Register  to view this content.

This UsedRange selects all of the data, and then resizes to omit the top row, and copies it all over at once.

[Richavlaues]

Thanks it worked. I also tried on my own by searching internet on how to replace recorded code with variables and I ended up with this:

Please Login or Register  to view this content.

It's working fine but will be glad if somebody optimize this code to single line or something better than this.

Thanks,
Richa

[daffodil11]

That's pretty good. I usually only declare variables if I'm going to use them a few times.

This works as well:

Please Login or Register  to view this content.

There are a few different copy and paste approaches. While you can copy and then paste on subsequent lines, the Copy method of the range object being used actually accepts the destination cell on the same line.

Such that

Please Login or Register  to view this content.

will copy a1 to b1.

The following line is doing the copy and the paste all in one go:

Please Login or Register  to view this content.

[Richavlaues]

Wow! You are awesome and thank You so much for help and also for explanation. It work great. Am marking this thread as solved.

Thanks,
Richa