How would I use VBA to find non matching values and input missing values from one workbook to another?

[Indominus]

Hello. So I have two workbooks that are used for this project. One referenced as pickorder. Another referenced as Routing. Sheet is DWP. Screenshots are attached. The pickorder is the one that needs to be correct since it gets uploaded to a site. Both lists all route codes in column B. . Sometimes however, a couple are not in the Pickorder but they are in dwp.

So what I need is to identify all values in the DWP sheet in Range("B2:B500"). Then identity all matching values in Range("B2:B500") of Pickorder. If it detects one not in Pickorder then input that missing value into the next open row on the Pickorder workbook in column B. So in this case MX115 and MX116. I also attached a screenshot showing what the final result will look like on the Pickorder sheet after. Thank you to anyone willing to help.

 

[Indominus]

Try:

For Each WB In Application.Workbooks
        If WB.Name Like "*PickOrder*" Then
            Set desWS = WB.Sheets(1)
        End If
    Next WB

This seems to work to identify the Pickorder workbook. The code runs however, it does nothing. The missing route codes are not pasted into the bottom of the Pickorder workbook. In column B

 

[mumps]

Try:

Sub FindUnplannedRoutes()
    Application.ScreenUpdating = False
    Dim desWS As Worksheet, srcWS As Worksheet, WB As Workbook, arr1 As Variant, arr2 As Variant, Val As String
    Set srcWS = ThisWorkbook.Sheets("DWP")
    For Each WB In Application.Workbooks
        If WB.Name Like "*PickOrder*" Then
            Set desWS = WB.Sheets(1)
        End If
    Next WB
    arr1 = srcWS.Range("B2", srcWS.Range("B" & Rows.Count).End(xlUp)).Value
    arr2 = desWS.Range("B2", desWS.Range("B" & Rows.Count).End(xlUp)).Value
    Set rnglist = CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(arr2, 1)
        Val = arr2(i, 1)
        If Not rnglist.Exists(Val) Then
            rnglist.Add Val, Nothing
        End If
    Next i
    For i = 1 To UBound(arr1, 1)
        Val = arr1(i, 1)
        If Not rnglist.Exists(Val) Then
            With desWS
                .Cells(.Rows.Count, "B").End(xlUp).Offset(1) = Val
            End With
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[Indominus]

Try:

Sub FindUnplannedRoutes()
    Application.ScreenUpdating = False
    Dim desWS As Worksheet, srcWS As Worksheet, WB As Workbook, arr1 As Variant, arr2 As Variant, Val As String
    Set srcWS = ThisWorkbook.Sheets("DWP")
    For Each WB In Application.Workbooks
        If WB.Name Like "*PickOrder*" Then
            Set desWS = WB.Sheets(1)
        End If
    Next WB
    arr1 = srcWS.Range("B2", srcWS.Range("B" & Rows.Count).End(xlUp)).Value
    arr2 = desWS.Range("B2", desWS.Range("B" & Rows.Count).End(xlUp)).Value
    Set rnglist = CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(arr2, 1)
        Val = arr2(i, 1)
        If Not rnglist.Exists(Val) Then
            rnglist.Add Val, Nothing
        End If
    Next i
    For i = 1 To UBound(arr1, 1)
        Val = arr1(i, 1)
        If Not rnglist.Exists(Val) Then
            With desWS
                .Cells(.Rows.Count, "B").End(xlUp).Offset(1) = Val
            End With
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

This works perfectly!!! Thank you so much. It is very much appreciated.

 

[mumps]

My pleasure.