Move or Copy Sheets - MS Excel

[EliseFreedman]

I currently have several different spreadsheets each in a different file.
Basically we have several members in our sales team.
I was asked to pick out several customers for each salesman and then find out
a) from our Top 50 products by volume sold, which products had they not purchased
b)a) from our Top 50 products by stock value, which products had they not purchased

This was reasonably straighforward

I now basically have 2 separate spreadsheets for each customer which are saved in separate subdirectories of the main directory
e.g
c:\LinesNotSold\ProductsByValue
within that subdirectory I have separate directorys for each salesman. The spreadsheets are then stored in the appropriate directory

I am now merging the worksheets so that I have a single spreadsheet for each rep containing 2 worksheets for each of his customers.
eg.
KR.xls
Sheet1=1012ProductsByValue
Sheet2=1012ProductsbyVolume
Sheet3=2114ProductsByValue
Sheet4=2114ProductsbyVolume

Basically, At present I am opening up each spreadsheet, rightclicking on the worksheetname and copying it to the master spreadsheet. Obviously this is timeconsuming as I have about 30 customers and about 6 salesmen.

Is there any way in which I can open up each spreadsheet in the directory in turn automatically and copy the first worksheet in the spreadsheet into the master worksheet.

Any help would be appreciated

 

[Stretchwickster]

Use the macro-recorder to record the process you are going through. Go to the VB Editor and modify the code to suit your needs (i.e. you probably want to construct a loop) then just run the macro.

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)