How can I create a macro for extracting data from multiple workbooks within a folder

I have a number of workbooks in one folder. I want to extract data from a specific cell from each workbook and paste it into a table in a new workbook within the same folder.

The cell containing the data has the same co-ordinates in each workbook.

For e.g. if I have 5 workbooks and the data is in a1 in each workbook, I want to create the following table

Workbook Data from cell A1
‘filename 1’ Data 1
‘filename 2’ Data 2
‘filename 3’ Data 3
‘filename 4’ Date 4
‘filename 5’ Data 5


How can I accomplish this?

Hi
You can use this formula for each workbook you want to copy from:
=[WorkbookName1]SheetName!$A1$1
=[WorkbookName2]SheetName!$A1$1
=[WorkbookName3]SheetName!$A1$1
=[WorkbookName4]SheetName!$A1$1
=[WorkbookName5]SheetName!$A1$1
The other workbooks do not need to be open.
Hope this helps.
Good luck.
Tony

Thanks for that - I can definitely see this working. However, I dont see how this method would make any real difference compared to opening up each individual workbook, copying the data in A1 and then pasting into the table in the new workbook.

I wanted to automate this formula. For example the macro would have detect how many workbooks there were in the folder, extract the info in cell A1 and paste it into the table in the new work book.

I have about 100 workbooks in this folder and it would take forever to do this manually.