VBA to copy data from multiple workbooks to Master Workbook

I have 4 workbooks which each contain a sales log. 1 is a Master Log and 3 are maintained by separate users. I am looking for the easiest way to update the Master Log with the data from the other 3 workbooks that is fast, user friendly and avoids data being corrupted by the users.

Open to suggestions other than the following, however if this is an appropriate solution would appreciate the help in writing the VBA...

Attached is a sample of how the data is ordered -- I have considered using named ranges and running a macro in the Master Log that would:-

1. Open Sales Person 1 Workbook
2. Copy Range1
3. Paste Range1 data (values only) in Master Log Range1
4. Close Sales Person 1 Workbook
5. Repeat for Sales Person 2 and 3 Workbook
6. Save and close Master Log

Will also need to consider ensuring each worksheet is sorted. All sheets are named the same in each workbook.

Thanks in advance.
Master Log.xlsx
Sales Person 1.xlsx
Sales Person 2.xlsx
Sales Person 3.xlsx

Should the data be copied one below the other in the Master workbook or in separate sheets? If its the former, should there be a separate column stating which book the data was copied from?

I checked the sales person's files. Will the data always be located in those rows? Or would they each start from row 3?

You do not need to use named ranges here. There is a faster, easier way of doing it through code. The sales person's can start entering their values just below the header, that is from row 3 onwards. The code will pick it up automatically.

Is there a particular reason why you need to sort it in the order of column B?

You can continue with column B, what you can do is have the numbers for the 2nd sales person start in row 3 with number 2 and then row 4 will have 2.01. This will then ensure that the macro works smoothly.

I will provide you the code shortly.

You can use this code. It also includes the sort option.

Please Login or Register  to view this content.

Do you mean that the Sales Persons log will also have entries that belong to the other sales people?

In that case, the code will need to be modified a bit. Will there always be 10 rows per person or is it variable?

Use this updated code -

Please Login or Register  to view this content.

Use this code -

Please Login or Register  to view this content.

You can retain the sort code or remove it if not required.

Yes, sure. Can you provide the path where the files can be saved? Or do you want the user to select that?

If you are not using the Sort code, then after this line

Please Login or Register  to view this content.

put this code -

Please Login or Register  to view this content.

This will resave the master file thrice with the new names.

Why do you want to save the sales person files as macro enabled? The macro would be residing in the master file wouldnt it?

You can do this - even before the save as code, put this line

Please Login or Register  to view this content.

This will save your Master file wherever you have saved it initially. It will then saveas to the 3 new names.

Np at all. Glad it worked. If your issue has been solved, please edit your original post and select go advanced, then select the prefix - Solved.