Find the matching row in two different workbooks

[imnotgoodatexcelyet]

Hello everyone,

I'm very new to Excel/VBA but I've been tasked with the automation of the manipulation of a report that we run several times per day. The manipulation is done, but now I need to figure out how to save my data to a specific and ever different spot on the master workbook.
We always run the report to include the previous day's data as well as what we have for the current day, so we always have some overlap on the data manipulated and the data in the master. We always manually select all the data and paste it over the beginning of the previous days data in the master file.
How would I write this in VBA? Since the data is always sorted the same way, my idea was to search for the exact match of the first row of manipulated data in the master workbook but I can't figure out how to do it.

Our data has 9 columns and I'd need to paste the entire set of new data, the rows vary every day.
Every problem that I've found similar to this always uses variables. I've managed to avoid them so far in this project and they confuse me like crazy, so if you could explain your work that'd be great.

Any ideas? Thanks in advance!

 

[imnotgoodatexcelyet]

Can you upload a copy of the file that shows what you want the data to look like after the sorting is done?

This is my current attempt CurrentAttempt.xlsx
This is what I need it to look like WhatsNeeded.xlsx

Notice how my current attempt has the data from two different days mixed together (44578 and 44579) for each user whereas the data that is needed will have all the data from 44578 sorted by user and then time and then continue to the next day. I think this is the difference between adding 3 separate sorts (what I currently have in my attempt) and adding a custom sort with 3 levels (what is in the final needed file). I can't figure out how to write a custom sort in VBA

 

[mumps]

Try:

Sub CopyData()
    Application.ScreenUpdating = False
    Dim v As Variant, i As Long, lRow As Long, srcWS As Worksheet, desWS As Worksheet, val As String, val2 As String, r As Long, c As Long, WB As Workbook
    Set WB = ThisWorkbook
    Set srcWS = WB.Sheets("Master Table")
    srcWS.Range("K:L").EntireColumn.Delete
    lRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Workbooks.Open "C:\Users\ghoneysette\OneDrive - Diel Jerue Logistics\Documents\Excel macro paste test\test.xlsx"
    Set desWS = Sheets("Sheet1")
    desWS.Range("K:L").EntireColumn.Delete
    For Each Rng In srcWS.Range("A1:J1")
        If val = "" Then val = Rng Else val = val & "|" & Rng
    Next Rng
    v = desWS.Range("A1").CurrentRegion.Value
    For r = 1 To UBound(v)
        For c = 1 To UBound(v, 2)
            If val2 = "" Then val2 = v(r, c) Else val2 = val2 & "|" & v(r, c)
            If val = val2 Then
                srcWS.Range("A1:J" & lRow).Copy desWS.Range("A" & r)
                lRow = desWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                With desWS.Sort
                    .SortFields.Clear
                    .SortFields.Add Key:=Range("A1:A" & lRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                    .SortFields.Add Key:=Range("C1:C" & lRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                    .SortFields.Add Key:=Range("D1:D" & lRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                    .SetRange Range("A1:J" & lRow)
                    .Header = xlNo
                    .MatchCase = False
                    .Orientation = xlTopToBottom
                    .SortMethod = xlPinYin
                    .Apply
                End With
                ActiveWorkbook.Close True
                WB.Close False
                Exit Sub
            End If
        Next c
        val2 = ""
    Next r
    Application.ScreenUpdating = True
End Sub

 

[imnotgoodatexcelyet]

Try:

Sub CopyData()
    Application.ScreenUpdating = False
    Dim v As Variant, i As Long, lRow As Long, srcWS As Worksheet, desWS As Worksheet, val As String, val2 As String, r As Long, c As Long, WB As Workbook
    Set WB = ThisWorkbook
    Set srcWS = WB.Sheets("Master Table")
    srcWS.Range("K:L").EntireColumn.Delete
    lRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Workbooks.Open "C:\Users\ghoneysette\OneDrive - Diel Jerue Logistics\Documents\Excel macro paste test\test.xlsx"
    Set desWS = Sheets("Sheet1")
    desWS.Range("K:L").EntireColumn.Delete
    For Each Rng In srcWS.Range("A1:J1")
        If val = "" Then val = Rng Else val = val & "|" & Rng
    Next Rng
    v = desWS.Range("A1").CurrentRegion.Value
    For r = 1 To UBound(v)
        For c = 1 To UBound(v, 2)
            If val2 = "" Then val2 = v(r, c) Else val2 = val2 & "|" & v(r, c)
            If val = val2 Then
                srcWS.Range("A1:J" & lRow).Copy desWS.Range("A" & r)
                lRow = desWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                With desWS.Sort
                    .SortFields.Clear
                    .SortFields.Add Key:=Range("A1:A" & lRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                    .SortFields.Add Key:=Range("C1:C" & lRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                    .SortFields.Add Key:=Range("D1:D" & lRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                    .SetRange Range("A1:J" & lRow)
                    .Header = xlNo
                    .MatchCase = False
                    .Orientation = xlTopToBottom
                    .SortMethod = xlPinYin
                    .Apply
                End With
                ActiveWorkbook.Close True
                WB.Close False
                Exit Sub
            End If
        Next c
        val2 = ""
    Next r
    Application.ScreenUpdating = True
End Sub

Is this going to paste the new data into the final workbook and then sort said data before saving and closing?
If it is, can we do it in this order: Delete the columns->sort the data->then begin the search for the first row in the test (the final workbook) workbook? We need to have it sorted before that way we can ensure the macro will search for the correct row

 

[mumps]

Is this going to paste the new data into the final workbook and then sort said data before saving and closing?

Yes.

See if this version does what you want:

Sub CopyData()
    Application.ScreenUpdating = False
    Dim v As Variant, i As Long, lRow As Long, srcWS As Worksheet, desWS As Worksheet, val As String, val2 As String, r As Long, c As Long, WB As Workbook
    Set WB = ThisWorkbook
    Set srcWS = WB.Sheets("Master Table")
    srcWS.Range("K:L").EntireColumn.Delete
    lRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With srcWS.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("A1:A" & lRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SortFields.Add Key:=Range("C1:C" & lRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SortFields.Add Key:=Range("D1:D" & lRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Range("A1:J" & lRow)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Workbooks.Open "C:\Users\ghoneysette\OneDrive - Diel Jerue Logistics\Documents\Excel macro paste test\test.xlsx"
    Set desWS = Sheets("Sheet1")
    desWS.Range("K:L").EntireColumn.Delete
    For Each Rng In srcWS.Range("A1:J1")
        If val = "" Then val = Rng Else val = val & "|" & Rng
    Next Rng
    v = desWS.Range("A1").CurrentRegion.Value
    For r = 1 To UBound(v)
        For c = 1 To UBound(v, 2)
            If val2 = "" Then val2 = v(r, c) Else val2 = val2 & "|" & v(r, c)
            If val = val2 Then
                srcWS.Range("A1:J" & lRow).Copy desWS.Range("A" & r)
                ActiveWorkbook.Close True
                WB.Close False
                Exit Sub
            End If
        Next c
        val2 = ""
    Next r
    Application.ScreenUpdating = True
End Sub

 

[imnotgoodatexcelyet]

Yes.

See if this version does what you want:

Sub CopyData()
    Application.ScreenUpdating = False
    Dim v As Variant, i As Long, lRow As Long, srcWS As Worksheet, desWS As Worksheet, val As String, val2 As String, r As Long, c As Long, WB As Workbook
    Set WB = ThisWorkbook
    Set srcWS = WB.Sheets("Master Table")
    srcWS.Range("K:L").EntireColumn.Delete
    lRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With srcWS.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("A1:A" & lRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SortFields.Add Key:=Range("C1:C" & lRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SortFields.Add Key:=Range("D1:D" & lRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Range("A1:J" & lRow)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Workbooks.Open "C:\Users\ghoneysette\OneDrive - Diel Jerue Logistics\Documents\Excel macro paste test\test.xlsx"
    Set desWS = Sheets("Sheet1")
    desWS.Range("K:L").EntireColumn.Delete
    For Each Rng In srcWS.Range("A1:J1")
        If val = "" Then val = Rng Else val = val & "|" & Rng
    Next Rng
    v = desWS.Range("A1").CurrentRegion.Value
    For r = 1 To UBound(v)
        For c = 1 To UBound(v, 2)
            If val2 = "" Then val2 = v(r, c) Else val2 = val2 & "|" & v(r, c)
            If val = val2 Then
                srcWS.Range("A1:J" & lRow).Copy desWS.Range("A" & r)
                ActiveWorkbook.Close True
                WB.Close False
                Exit Sub
            End If
        Next c
        val2 = ""
    Next r
    Application.ScreenUpdating = True
End Sub

That's it! Thank you so much, you were a lifesaver for this whole process

 

[mumps]

My pleasure.