Macro to extract the first WS from 5 WB

Hi There,

Really hope you can help,

I have been asked to look into making a master spread sheet which will contain the first Work Sheet from 5 different Workbooks.
The 5 workbooks are all laid out with the same columns but have a different number of rows which will fluctuate through out the month.

The master spread sheet should have an extra column (A) that states the source name of the workbook it came from.

I am completely new to Macros and came across this site by Googleing yesterday.

Doug

Hi Richard

Basically the five sheets are price catalogues from our main suppliers.

I have hopefully managed to attach an extract of one of the daughter workbooks. I have changed names and prices.

Hi Richard,

Thanks for your help, When I try and use the macro it stopping on this line

Workbooks.Open Filename:=Range("Path") & "\" & Range("catalogues").Cells(c, 1)

All I did was save the file down and put in the new path "S:\accountants\Doug\Month End" which contains the file Catering.

The Run time error '1004':

Microsoft Excel cannot access the file 'S:\accountants\Doug\Month End\Catering.'

Thank you Richard,

I worked out I needed & ".xlsx" at the end of the line.

Workbooks.Open Filename:=Range("Path") & "\" & Range("catalogues").Cells(c, 1) & ".xlsx"