macro for importing sheets from multiple workbooks into a master workbook

I am not very good with macros or vba code and need advice.

First this is what I want to do. I have a Master workbook lets call it master.xls. It has multiple worksheets - related to managers names : name1, name2, name3 etc. I have a workbook for each person name1.xls that has a worksheet in it named sales. I want to run a macro that will open each workbook name1, name2 etc and copy all the data on the sales worksheet into the Master workbook sheet. So the macro will clear the name1 sheet in the master, then open the name1.xls file, then copy the sales sheet in name1.xls, then paste it into the name1 sheet into the master.xls, then close the name1.xls file and start the process over again for each manager.

I tried to do this using the record macro feature. I recorded myself clearing each sheet, opening the file, copying, pasting, clearing clipboard, closing file, and then moving on to the next sheet and doing the same. I had it working just fine until data was updated on the sales sheet of name1.xls. When running the macro it didn't bring in any of the new data to the sheet.

So I am not sure what I am doing wrong or how to fix it.

Can anyone help?

Thanks Hannah

Perhaps a macro like this? You need to change the line "E:\Temp\" to suit your needs.

Please Login or Register  to view this content.

Alf