VBA: Copy non-blank and paste to separate sheet ONLY blank cells (running list)

Hello!

Hoping someone can help! I would like to find a formula that can do the following:
Sheet 1 has data in columns A & B, each week a different amount of data will show.
Sheet 2 is where I want the data to paste weekly in a running total in columns A & B.

Therefore this is what I want:
A macro that will copy columns A & B (ONLY THE CELLS THAT CONTAIN DATA) and then paste the data on the first line of blank data on Sheet 2.

So for example:

If week one had this on Sheet 1
Dog
Cat
Bird

Hit the macro and (Dog, Cat, Bird) will end up on lines 1-3 on Sheet 2.

Then week two had this on Sheet 1
Cow
Pig
Duck

Hit the Macro again and will end up with lines 1-6 on Sheet 2:
Dog
Cat
Bird
Cow
Pig
Duck

So in other words, the macro ONLY copies cells with data and pastes BELOW the last line of data in week 2 (first "open" cell).

Would really appreciate help!!!

Assuming u have no empty lines inbetween:

Please Login or Register  to view this content.