How to create a Master Sheet using multiple worksheets from different files

[Spuddy77]

Hi, I am trying to create a master sheet that copies all of the data from 6 other worksheets saved in different files. Each worksheet contains data from columns A1 to CY down to approximately 145 rows. The column headers are all the same (as in the first 4 rows on each worksheet contain the same data).

I want the master sheet to contain the first 4 rows and then the data from each sheet to be pasted underneath. Basically at the moment I am copying and pasting the entire contents of each worksheet and pasting it into a single (master) sheet. One after the other so I end up with 665 rows.

Is there a formula / v look up or macro that can do this for me???? I've looked up a few macros but I don't think I'm getting the range correct and I don't know how to do sheets from different workbooks...?

Any help would be so appreciated!!!!

[Alf]

I think a macro could do the job but there are several questions that need to be answered in order to write a macro to do this.

copies all of the data from 6 other worksheets saved in different files

So are there 6 files each with only one worksheet you wish to copy data from? Or do each file contain two or more sheets with the information you need. If so are the sheet name identical for the different files?

Are all the different files placed in the same folder? If so are there other files in this folder.

Is there a structure or a repeating pattern to the file names?

Adding data to you "Master sheet" is this something you would do on a regular basis. If this is so should the old data be kept and the new data be entered below it?

Alf

[Spuddy77]

Hi Alf, thank you for your response, here are my answers to your questions. Thank you so much for helping me out!

All 6 files have 2 worksheets. Only the first worksheet requires the copying to a master sheet - the name of this worksheet differs in each file.
All 6 files are saved in different folders with other files in the folders.
All files contain "Activities and Tasks for Programs" in the file name but also have a specific program name in the title.
The data would be added to the individual sheets and I would need to run the macro each time I need an updated version - essentially replacing / refreshing the entire master.

If it would be easier or better, I could collate all of the sheets into one workbook (and educate the users to go to one folder, select the master file and select their correct tab).

Thanks again!

[Alf]

The easiest would be if you could move all the six files to a specific folder and if this folder contains only these 6 files their names does not matter. I've written a macro for extracting data from all the files in a specific folder "C:\Myfolder\" activating the first sheet in every file and copying all the data in that sheet minus the first four rows. This macro assumes the 6 files are all ".xlsx" files

You master file should contain the four rows with headings. So can you upload one of the sample files after removing any sensetive data, I could then do a bit of testing.

Please Login or Register  to view this content.

Alf