Copy data from all workbooks/sheets in a folder

[photobeans]

Hi all.

Here's what I'm trying to accomplish.

I have multiple workbooks in the same folder, and within each workbook will be a varying number of worksheets. The name of the worksheets will be based on a certain cell in each sheet, so they'll be dynamic. The layout of every sheet is exactly the same.

I want to be able to run a macro that will:

1. Open every workbook in the folder
2. Run through every worksheet for each book
3. Copy data from a certain cell on each sheet
4. Paste this data in a new workbook

I've tried tons of searches, and found code that would at least open each workbook in a folder, but I couldn't find anything that went through every sheet, or copied specific cells.

Anyone willing and able to set on the right course?

Thanks much in advance for reading!

 

[Glory]

2) You want to refer to sheets based on their index value, not their name.

For each theSheet in ActiveWorkbook.Sheets
   'code involving theSheet
Next

Or

For i = 1 to ActiveWorkbook.Sheets.Count
   'code involving ActiveWorkbook.Sheets(i)
Next

3) How will you know which cells to copy?

4) Workbooks.Add

 

[photobeans]

Thanks for the reply.

I'll know the cells because there will be specific ones that will have the data I need. All the sheets will have the same layout, so I'll know which ones I want to get.

Thanks!