Macro to combine three spreadsheets into 1

[joyhampton]

I have three separate documents that are updated on constant basis by 3 different people.
Those three documents are linked to a 4th document and will update every hour.
I need to summarize this information automatically for dashboard purposes.
I can't use a pivot table because most of the data is text so a pivot table from multiple ranges does not work.
I wrote a macro to consolidate the data and it works. I can use that data to pivot from there. But I can't repeat that macro.
I need a way to update that consolidated information. as the the three sheets are updated.

Macro below: sample workbook attached.
Thanks for your help!

Please Login or Register  to view this content.

Moderators note: code tags added for you - this time

[FDibbins]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

[AB33]

The code consolidate all sheets, except sheet 1 in to sheet1.

[joyhampton]

Thanks AB33... I think that works!

[joyhampton]

AB33 I can get the code to work, but I'm having trouble translating it to work in my actual workbook.
Can you walk me through the code? I'm going to run this code in the same workbook for 4 sets of 3 worksheets.

[AB33]

Use this line

Please Login or Register  to view this content.

which says consolidate the sheets in the array. Change the names of the sheet to suit your needs.

[joyhampton]

Thanks!
And do I have to run the macro each time I want to refresh or will it be automatic?
I'm sorry if this was a remedial question.

[AB33]

No,the code is not auto, you have to run it.
I have Created a button on the master sheet and link it to the code so that you do not need to go in to the module to run it, just press it to run it.
The code first clear the data on the master sheet(Sheet1) except the header.