AlexMason
10-26-2009, 08:49 AM
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
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