update master excel sheets from child sheets

I currently have a master sheet that I am compiling all audit results in, however this is becoming clumsy to use and it exposes some findings between regions that i may not wish to do.

I want to give out individual worksheets to each region and when they update their copy it automatically updates the master. If something is changed in the child then just the changed date should be updated in the master, if something new is added then this record is added in full.
Each row in each sheet has a unique reference number (Unique between sheets also) so that I can identify individual rows.

I am not sure what is the most appropriate method, I have seen some posts looking to achieve this or part of, however I cannot get my head around how I apply it to my requirements.

Any help would be greatly appreciated.

I have attached examples of the master and child workbooks.

Thanks in advance.

Start by copying the headers in rows 1 and 2 from the individual file to the Master. This method assumes that the Master file and individual file are both open. It assumes that the unique reference number is in column H. Copy and paste this macro into the worksheet code module for the first individual file. Do the following: right click the tab name for your Non-Conformities sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Repeat this for every individual file so that they each contain the macro. Also, in all the individual files, delete all the unused unique reference numbers in column H. The unique reference number in column H should be added as needed.

Please Login or Register  to view this content.

As you enter data in the individual files, that data will be automatically copied to the Master.

Replace the previous macro with the code below. This version forces you to enter new data in the first available blank row. It also generates the next Ref number. Because the macro is a worksheet change event, it wouldn't work to have the Master automatically open when data is entered into the "child" worksheet. However, we could open the Master automatically when the "child" workbook is opened. If that works for you, are the child files and the Master file saved in the same folder? If not, what is the full path to the folder where the Master is saved?

Please Login or Register  to view this content.

To be honest, I don't have any experience working with SharePoint. My idea was to insert a Workbook_Open event into the code module for ThisWorkbook in the child files so that when each file was opened, it would also open the Master.

Place this code in the code module for ThisWorkbook. Do the following for each child file: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the left hand pane, double click on "ThisWorkbook". Copy/paste the code into the empty window that opens up. Close the window to return to your sheet. Save the file, close it and then re-open it.

Please Login or Register  to view this content.

My pleasure.