Code that extracts cell contents from specific cells

Hi, I have 8 Workbooks with about 20 Worksheets each and need a code that list the name of each Worksheet in a Workbook (some are hidden), then list the contents of a specific cell that is part of the worksheet name, then list the contents of a several cells in each worksheet that are exactly the same on a new worksheet within the file or creates a new Workbook that pulls from each of the 8 Workbooks and lists them in that workbook. I would want the finished product to look like attachment...

Thank you.

Originally Posted by dflak

Let me state the requirements in my own words to see if I understand it. You want to go through a directory and open up workbooks in the directory. Do you want all the workbooks or workbooks that match a certain name?

Then you want to list all the sheets in the workbook. Are there any sheets you don't want to look at?

Then you want the contents of cell D6 and the other 39 cells listed, presumably in the format you outlined on the sample workbook.

Is this correct?

Hi Dflak, The first part is correct. However I have no issues working within each workbook (since there are only eight) and keeping the information from each workbook on a new sheet within the existing workbook. The workbooks have different names.

I don't need a separate list of the worksheets rather than I would want the name of each worksheet listed before the data, so you know which sheet the data belongs to since it’s the same group on every worksheet.
For example in the workbook, there are worksheets named Apples, Oranges, Bananas, Peaches, Grapes, Mango, Papaya, Each one of these sheets have a couple hundred lines of data. I want a "snapshot" or "summary" sheet that gives the highlights or the pieces of info we need the most for an audience that can't go through all of the data.
I don’t need every worksheet in the workbook...so it would be a range like 'Apples:Papayas' However in the current workbook I’m in that I need this for, it would be 55 out of 60 worksheets (basically everything minus control and input tabs etc)
So once the data is pulled from each worksheet, I would need the worksheet name, plus a name box on the sheet so you know where the numbers belong-
(The attached file is updated so it gives more detail. The Summary All worksheet accurately summarizes what’s needed as the outcome.)

Should be on previous post

Hi, Thank you. This works perfectly on the file you sent but when I tried to change it, I get run time errors.
errors. I think its ok if this runs off of all worksheets within the workbook and all workbooks in the directory specified. Do you have to add the names of the workbooks to the code?