Copy Data From Multiple Open Workbooks to Another Workbook with Separate Worksheets

I found this code on another thread
http://www.excelforum.com/excel-prog...-workbook.html

Please Login or Register  to view this content.

The thing is that I am pulling files from SharePoint, opening each of them but not saving them because I don't need to save them. I just need to copy the data from "Sheet1" for each file and put the data on separate worksheets on another workbook.
Right now, all the data is going to one worksheet but I need the data of each workbook "Sheet 1" to go to separate worksheets.
If someone can help me with this will be greatly appreciated.

yes, they are in separate excel windows.