Importing data form other workbooks

Hi Guys

From all your experience, what is the easiest and quickest way to consolidate many spreadsheets into one master spreadsheet?

Copying and pasting just takes ages and vlookups are not up for the job due to the volume and number of different spreadsheets. Is there a macro that can just pick password prottected sheets up and paste into a master sheet?

Basically I have say 10 to 15 spreadsheets that managers have to comment on sales figures on their area then I have a master spreadsheet that I want to consolidate everyone’s results and comments on one page rather than opening each area spreadsheet.

Many thanks for your help.

This is a very common request. Have a search through the forums and you'll get plenty on answers.

Alternatively Jerry Beaucaire has loads of examples of things like this on his site. In your case, probably something like https://sites.google.com/a/madrocket...s-to-one-sheet

Thanks Kyle for your advice.

I have done many searches however just wondering from peoples experience what they thought what the best way was?

I will check the website out to see if this helps me.

Thanks :-)

Jerry's solutions is the general one and most that you see will be variations on this theme. There are other ways that would be more efficient as they don't need to open each workbook, however they require more code and are less flexible

I think this would be much better. If the macro didnt have to open the spreadsheet each time it would be a lot quick due to the size of the spreadsheets. Could you point me in the direction of how to do this? Many thanks.

Sure, have a look here http://support.microsoft.com/kb/257819, be warned however that unless you are comfortable with macros you would best be sticking to Jerry's solution. The linked approaches the problem with ADO and querying the excel sheet as a database, this is very fast, but can get a bit in depth if your data isn't arranged as a table