Search through multiple excel files and get data

[klunker]

Hi All,

I'm currently using the following code to "pull" a range data from a closed workbook.

Please Login or Register  to view this content.

This works well if I know which workbook and sheet I will be getting the info from. What I would like to do is add a function that searches through a few workbooks looking for a worksheet name that matches a value in a cell in my original workbook.

ie. Workbook 1, cell A1 = "Jelly"

search through excel files for the workbook that contains the worksheet named "Jelly" and then copy a range from that worksheet back to workbook 1.

Does this make sense? Thanks for any help you can provide!

[alansidman]

Will the path to the folder having the workbook name in A1 always be the same?

[klunker]

Hi Alansidman,

No the path will change, but it will always match the path of the master workbook. So it would be this type of format;

C:\Users\desktop\folder1\master.xls
C:\Users\desktop\folder1\pulldata1.xls
C:\Users\desktop\folder1\pulldata2.xls

So folder1 could be any name but they would always be together and they would always be in the same folder as master.xls

I'd like to be able to have the command button in the master.xls and have the code search through pulldata1.xls and pulldata2.xls for a sheet called "Jelly" once found it would pull a range of data from that sheet, say A1:W20 (as examples).