Summarise information from multiple worksheets in multiple files

[alistair_]

Hi All,



I'm a novice with Excel and new to the forum so I apologise if the post isn't clear.

I have a series of excel files in one folder. Each excel file contains information about a different material property i.e. unit weight, specific gravity etc. The material property is the file name.

Each file has a series of worksheets which contains information for different material types i.e. material 1, material 2 etc. The material name is the tab name. Within these worksheets I have calcuated the minimum, maximum, mean and standard deviation and these are always shown in the same cell.

I would like to use a VBA script in excel, so that I select the folder location and excel opens each individual file within the folder, record the file name, and for each tab, records the tab name summarise the information shown in these cells A3&4, B3&4, C3&4, & D3&4 (note cell 3 will be text and cell 4 an number). It would ideally loop through each file in folder and summarise the information into one excel table.

I was wondering whether anybody could help me out with a code to show a table like that below.

Any help would be greatly appreciated

Thanks


File Name i.e. Unit Weight
Tab 1 Name - Cell A3 - Cell A4
Tab 1 Name - Cell B3 - Cell B4
Tab 1 Name - Cell C3 - Cell C4
Tab 1 Name - Cell D3 - Cell D4

Tab 2 Name - Cell A3 - Cell A4
Tab 2 Name - Cell B3 - Cell B4
Tab 2 Name - Cell C3 - Cell C4
Tab 2 Name - Cell D3 - Cell D4

File Name i.e. Specific Gravity
Tab 1 Name - Cell A3 - Cell A4
Tab 1 Name - Cell B3 - Cell B4
Tab 1 Name - Cell C3 - Cell C4
Tab 1 Name - Cell D3 - Cell D4

Tab 2 Name - Cell A3 - Cell A4
Tab 2 Name - Cell B3 - Cell B4
Tab 2 Name - Cell C3 - Cell C4
Tab 2 Name - Cell D3 - Cell D4