Combining 2 excel sheets for a live document

Question

Hi

I am wanting to use a largish excel sheet as a live document to monitor assembly parts, their quantities, their location etc.

Now, if project management didn't need to issue updated parts lists, then the guy on site would just keep this master document and perform all the house keeping himself as and when the parts arrive.

however, there may be a need for project management to update this master list (on a weekly basis). this update could contain more parts, less parts or the same number of parts but with altered attributes.

the main problem however is consolidating the two worksheets (ie merge the update into the master) without overwriting those parts which the site manager has already received and accounted for.

the code i have written is crude, but works.... however it cant handle it if the lists are different sizes. i.e. it wont add in additional parts, and wont remove parts that no longer exist in the update. 

heres the code

Sub Consolidate() 
     
     
    Dim CheckRow, ListRow As Integer 
    Dim rMax, rMax2 As Integer 
    Dim OldPart As String 
    Dim NewPart As String 
    Dim Selection1 As String 
    Dim NUM As Integer 
     'If you tab out your functions like this then it is easier to see how the functions are organised
     
     
    NUM = 0 
    rMax = FindLastRow1(3) 'finds the number of rows in master parts list
    rMax2 = FindLastRow5(3) 'finds the number of rows in the check list
     
    For CheckRow = 2 To rMax 'Cycle through the check
         
         'Application.ScreenUpdating = False
         
        If Sheets("KSL PARTS").Cells(CheckRow, 9) = "" Then 'If the check list line is blank
             
            For ListRow = 2 To rMax2 'Cycle through the list
                 
                If Sheets("File Merge Sheet").Cells(ListRow, 1) = Sheets("KSL PARTS").Cells(CheckRow, 1) _ 
                And Sheets("File Merge Sheet").Cells(ListRow, 2) = Sheets("KSL PARTS").Cells(CheckRow, 2) _ 
                And Sheets("File Merge Sheet").Cells(ListRow, 3) = Sheets("KSL PARTS").Cells(CheckRow, 3) Then 
                     'One big AND function, can be broken down if needed, this compares the first 3 columns between sheets
                     
                     'MsgBox "Match found: Row " & CheckRow & " matches row " & ListRow
                     
                     
                    NewPart = ListRow & ":" & ListRow 'Makes a Cell location for updated row
                    OldPart = CheckRow & ":" & CheckRow 'makes a cekk location for row to be updated
                     
                    Sheets("File Merge Sheet").Select 'activates the check list
                    Range(NewPart).Select 'selects the updated row
                    Selection.Copy 'copies
                    Sheets("KSL PARTS").Select 'selects master list
                    Range(OldPart).Select 'selects the row to be updated
                    ActiveSheet.Paste 'pastes the update over the old row
                    NUM = NUM + 1 
                     
                End If 
                 
            Next ListRow 'move onto next checklist row and repeat
             
        End If 
         
    Next CheckRow ''after checking all checklist rows against single row on master list, cycle to next master list row, rinse and repeat
     
    Selection1 = "2" & ":" & rMax2 
    Sheets("File Merge Sheet").Select 
    Range(Selection1).Select 
    Selection.Delete 
     
    Application.ScreenUpdating = True 
     
    MsgBox "Done: " & (NUM) & " Parts Updated" 
    Sheets("KSL PARTS").Select 
     
End Sub 

this basically checks the "location" cell in the master list to see if a part needs updating. the rationale being that the site guy will have added the parts location to the sheet if he has received it. if he's received it then it shouldn't need to be changed.

you may also notice that, even on a fast machine...this takes a good few minutes to perform when you have over 1000 rows because it is linearly comparing all rows to all rows between sheets. 

any help would be much appreciated. thanks, Alex