Select file, Copy Worksheet Range and Append onto 1 Sheet

[Tellm]

I've searched the forum with little success so I am having to write to you all for help. I need a macro that allows me to select from a directory either an xls or xlsx file. Once selected I need the macro to copy all of the data from the range A2:Q800 from all of the worksheets which have data in them and append them onto sheet2 from where I invoked the macro. The sheet names and quantity of sheets (never greater than 5) will be completely different within each of the files opened. If anyone can help it would be greatly appreciated.

Thanks

[davesexcel]

Wow, I could actually here your accent when reading your post.
Will the folder only have the workbook files in it and will the workbook that you activate the code be in that forlder as well?

Check out this thread in the meantime
http://www.excelforum.com/excel-gene...worksheet.html

[Tellm]

Dave, firstly thanks for coming back - it's possibly an age thing as to why you can hear my accent. In answer to your questions; the folder could be saved anywhere on the c:\ drive and the workbook will not be in the same location. So as not to confuse things it will only ever be a single xls or xlsx file that I select, never multiples.

Thanks

[davesexcel]

Alright, I was picturing something bigger.
I shall return.

[davesexcel]

Hi Tellm,
The zipped folder has three workbooks, one is the workbook that collects the info and two are sample workbooks each being an .xls or .xlsx file, and having data in A2:Q800 ranges on each sheet.
The one with the code is a macro enabled workbook and will not even show up on your list when you run the code.

When you click the button, you will open a getopenfilename dialog box. The filter only shows .xls or .xlsx files.

Open one of the workbooks and watch the magic!!!

Actually... there will be no magic to watch, once you open the workbook, it will close right away and you will wonder if anything happened.

Please Login or Register  to view this content.

Hope everything works out.

Be sure to change the file directory

[Tellm]

Dave, once again many many thanks - this is exactly what I needed.