open workbooks and pull data

[viceb]

I am trying to create a sheet that opens a workbook...the name of that workbook is a calculated name, and pull data from a fixed cell, then close the sheet and move to the next workbook..ending up with a table of pulled data.

For example,
A B C D E F
1 TS160515 TS160508 TS160501 TS160424 TS160417
2 Emp1
3 Emp2
4 Emp3
5 Emp4
6 Emp5


Cells B1,C1,D1 etc are calculated. The file name is the data in cell A2 + B1. In ths case: TS160515Emp1. I need to open that sheet name, grab data from cell d20 and put it in B2, then close the sheet and open the next sheet which is named A2 + c1, D1, etc. Repeating that procedure...open that sheet, grab D20 and close the sheet.
So I will end up with a table of data pulled from those sheets.

A B C D E F
1 TS160515 TS160508 TS160501 TS160424 TS160417
2 Emp1 $15.00 $20.00 $15.50 $27.00 $14.20
3 Emp2
4 Emp3
5 Emp4
6 Emp5

I am trying:
Workbooks.Open Filename:= _
"B:\dataSheets\Costing\TS160320Emp1.xlsm"
Range("D20").Select
ActiveWindow.Close
End Su
But i keep generating an error.


Forgive me if this sounds like it should be easy. I haven't created a sheet for a couple of years and during that time I had a stroke which seems to be throwing me off on some things that should be simple.

Thanks

 

[viceb]

I should add, I tried to just create the sheet and have the cells populated with the manufactued names:
=A2 & B1 & ".xlsm" & Daily!$I$55
but the result was the file name & the pulled data and I only want the pulled data. Meaning, I want the formula to be the filename and not part of the result.
But in either case, that would only work if I had all of the sheets already loaded. I know I had to use a macro to open the sheet, pull the data dn close the sheet. Otherwise, I'd have way to much memory used as I have about 200 sheets to open (based on #of employees * dates)