Pull data from closed workbooks

[sdgenxr]

What is the best way to copy worksheets from a closed workbook into the current open workbook? I've done a little digging around and found the below code but I don't see where it places the data? Also, I'd like to have an if loop to create new worksheets if needed.

Thanks

Please Login or Register  to view this content.

[sdgenxr]

Ok, I've rewrote most of the code but I'm getting a little lost on the best way to loop through multiple workbooks and worksheets. To make it a little simpler, I would only need to pull data from one worksheet from multiple workbooks.

Please Login or Register  to view this content.

[Norie]

Do you want to loop through all the Excel files in the folder FileLocation points to and copy the data from Sheet1 in each?

Where in the destination workbook do you want to put the data?

[sdgenxr]

Do you want to loop through all the Excel files in the folder FileLocation points to and copy the data from Sheet1 in each?

Where in the destination workbook do you want to put the data?

Yes, I would like to loop through all the Excel files in the FileLocation folder and copy the data from a defined sheet into a new named sheet in the destination workbook at the end of any existing sheets.

However, the way I have this envisioned is that the user would select a new worksheet in each workbook to pull information from and overwrite any existing worksheets. It might be easy to name the new destination worksheet as the name of the origination workbook.

I hope that makes sense.

[mc84excel]

I've found the following helpful for reading values from a closed workbook. HTH

http://spreadsheetpage.com/index.php...a_closed_file/

[sdgenxr]

I've found the following helpful for reading values from a closed workbook. HTH

http://spreadsheetpage.com/index.php...a_closed_file/

I've looked over that page already (see first post for code) and didn't understand how and where it was placing the information into the existing workbook.

[Norie]

Sorry, it's still not clear what you want to do, especially the second part.

Looping through each workbook and copy the data from, or copying the sheet itself, a specific, named worksheet would be relatively straightforward.

However looping through and having the user select which sheet to copy from would be more complicated.

For one thing a method to allow the user to select the required sheet would have to be devised.

[sdgenxr]

I already have the method for a user to select a specific sheet in the workbooks using vlookups and a selection box.

Given that looping through each workbook and copying data from is relatively straightforward, how far off am I with the code in the second post, or should I just use something that is already created?

Thanks again for all the excellent help here!