Dynamic Data Combination

Hi,

I have three worksheets that have rows being consistently added to, and values being updated.
I would like to have one 'Combined' worksheet that has all of the data from the three worksheets combined in a large list.

E.g. Worksheet 1:
Computer
Laptop
Mouse

Worksheet 2:
Desktop
Keyboard

Worksheet 3:
Phone
Cable
Charger


Combined:
Computer
Laptop
Mouse
Desktop
Keyboard
Phone
Cable
Charger

At the moment the data is just copy and pasted across every time there is a change, but I was hoping there would be a way to dynamically link all of this across to the combined worksheet automatically, including the additional rows.

Thank you,
zbradley

Hello ZBradley,

Do we assume that there is only one column, say Column A, of data to be combined?

Cheerio,
vcoolio.

Hi vcoolio,

Thanks for the reply, sorry I should have been more specific.
There are actually many columns of data to be combined, 39 additional columns.
Would this have to be done via a macro?

Thanks,

zbradley

Hello ZBradley,

Yes. A macro would be the simplest, more efficient method especially considering the number of columns.

I'll be back.

Cheerio,
vcoolio.

Hello ZBradley,

Try the following code in a copy of your work book first:-

Please Login or Register  to view this content.

I have named the sheet where you want to consolidate all data as "Master".
The code will cover all sheets in your work book even ones that you may add in future.
I've attached my test work book for you to play with. You'll notice that I've only taken the data through to Column K but the code will cover all columns that you may have with data in them. Click on the run button to see it work.

Do you need the "used" data in each individual sheet cleared once the transfer is completed or do you need the data to stay in each individual sheet and just "refresh" the "Master" sheet each time a transfer is completed? You'll need to do one or the other otherwise you will end up with much duplication in the "Master" sheet.

I hope that this helps.

Cheerio,
vcoolio.

Hi vcoolio,

Thanks so much for your help!
The macro helped a lot, I played around with it a bit and got it working.

You guys on here are the best

Cheers!

zbradley

Hello ZBradley,

You're welcome. Glad that I could help.

Cheerio,
vcoolio.