Pull data from external sheets based on input

[Scott13]

So a little backstory. I a work book for some equipment. Each work book is in its own directory. Each workbook has 54 Sheets. So that is 30 workbooks in 30 seperate directories. Each workbook is setup exactly the same, # of cells, rows, columns and formatting. The only difference in is the values of certain cells.

I'm looking for a way, probably some VBA, to use a dropdown or two to determine what serial number of equipment and what sheet to pull this information from into a template.

So what I need is a 31st Sheet to pull this data into, then I can copy and paste it to where I need it. I know this may seem odd but its the solution i'm looking for. Each of the 30 workbooks have VBA preventing someone from copy and paste it directly, and I dont want to remove that when I need to extract the data out of the workbook.

I hope this makes sense.

Thanks!

 

[cpatel13]

So a little backstory. I a work book for some equipment. Each work book is in its own directory. Each workbook has 54 Sheets. So that is 30 workbooks in 30 seperate directories. Each workbook is setup exactly the same, # of cells, rows, columns and formatting. The only difference in is the values of certain cells.

I'm looking for a way, probably some VBA, to use a dropdown or two to determine what serial number of equipment and what sheet to pull this information from into a template.

So what I need is a 31st Sheet to pull this data into, then I can copy and paste it to where I need it. I know this may seem odd but its the solution i'm looking for. Each of the 30 workbooks have VBA preventing someone from copy and paste it directly, and I dont want to remove that when I need to extract the data out of the workbook.

I hope this makes sense.

Thanks!

Well it's bit confusing, could you please elaborate and if possible provide a sample file