Extracting specific cell values from multiple worksheets

[jdhfch]

Hi,

Please can someone help?

I have a folder with around 1200 individual XLSX files in, all the same format, but with different file names.

I am wanting to extract some info in only key cells in each file into a master file:

1. Cell D1 - Name
2. Cell D2 - Cust Number
3. Cell D5
4. Cell E65
5. Cell F65
6. Cell G65
7. Cell H65
8. Cell I65

An example layout would be:

Name	Cust Number	Cell D5	Cell E65	Cell F65	Cell G65	Cell H65	Cell I65
Example	1234	Cola	Plain Crisps	Plain Nuts	Still Water	Biscuits	Bee

Is there any easy way to do this that can be updated frequently?

I am using Excel 365

Many thanks

 

[jdhfch]

Change

    ThisWorkbook.Sheets(1).[a1].CurrentRegion.ClearContents

to

    ThisWorkbook.Sheets(1).[a1].CurrentRegion.Resize(, 9).ClearContents
    ThisWorkbook.Sheets(1).[a1:i1] = Array("Filename", "Client no", "Client name", _
        "Manager", "list 1", "list 2", "list 3", "list 4", "list 5")

Perfect - thank you!

 

[Fuji]

You are welcome and thanks for the feedback.

 

[Joe4]

jdhfch,

Please Note: When marking a post as the solution, please mark the post that contains the solution (not your own post acknowledging that some other post was the solution).
When a post is marked as the solution, it is then shown right underneath the original question so people viewing the question can easily see the question and solution in a single quick glance without having to hunt through all the posts.