Find the matching row in two different workbooks

imnotgoodatexcelyet

New Member
Joined
Jan 17, 2022
Messages
27
Office Version
  1. 2019
Platform
  1. Windows
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

New Member
Joined
Jan 17, 2022
Messages
27
Office Version
  1. 2019
Platform
  1. Windows
The problem is being caused by the "#N/A" in columns K and L because Excel recognizes "#N/A" as an error. Are these values results of a formula or can they be replaced with something else.
Rows K & L can actually be deleted entire from both workbooks. That's a step that I missed in my initial macro
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
12,421
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
This macro will delete columns K and L.
VBA Code:
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
    Set srcWS = ThisWorkbook.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)
                Exit Sub
            End If
        Next c
        val2 = ""
    Next r
    Application.ScreenUpdating = True
End Sub
 
Solution

imnotgoodatexcelyet

New Member
Joined
Jan 17, 2022
Messages
27
Office Version
  1. 2019
Platform
  1. Windows
This macro will delete columns K and L.
VBA Code:
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
    Set srcWS = ThisWorkbook.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)
                Exit Sub
            End If
        Next c
        val2 = ""
    Next r
    Application.ScreenUpdating = True
End Sub
YES! That worked

The last thing that I'm trying to do is close the two workbooks. I want to save test, but not save the other one

VBA Code:
Workbooks("test.xlsx").Close savechanges:=True

Workbooks("WholeMacroAttempt2.xlsm").Close savechanges:=False

Is this right?
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
12,421
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
Try:
VBA Code:
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)
                Exit Sub
            End If
        Next c
        val2 = ""
    Next r
    ActiveWorkbook.Close True
    WB.Close False
    Application.ScreenUpdating = True
End Sub
 

imnotgoodatexcelyet

New Member
Joined
Jan 17, 2022
Messages
27
Office Version
  1. 2019
Platform
  1. Windows
Try:
VBA Code:
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)
                Exit Sub
            End If
        Next c
        val2 = ""
    Next r
    ActiveWorkbook.Close True
    WB.Close False
    Application.ScreenUpdating = True
End Sub
For some reason this doesn't close either workbook. Neither did my attempt at it.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
12,421
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
Try:
VBA Code:
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"
    Workbooks.Open "C:\Mario\Forum Help\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

New Member
Joined
Jan 17, 2022
Messages
27
Office Version
  1. 2019
Platform
  1. Windows
Try:
VBA Code:
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"
    Workbooks.Open "C:\Mario\Forum Help\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 worked perfectly. You're a wizard, thank you so much
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
12,421
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
You are very welcome. :)
 

imnotgoodatexcelyet

New Member
Joined
Jan 17, 2022
Messages
27
Office Version
  1. 2019
Platform
  1. Windows
Good morning,

I have one more issue with this macro. The workbook that we're pasting into has the following sort:
Sort by Column C Smallest to Largest
then by Column A A to Z
then by Column D Smallest to Largest

How do I write the script for this? When I try to sort them one at a time using
VBA Code:
.Sort key1:=.Range("C2"), order1:=xlAscending
.Sort key1:=.Range("a2"), order1:=xlAscending
.Sort key1:=.Range("D2"), order1:=xlAscending
the order does not end up the same.

Any thoughts?
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
12,421
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
Can you upload a copy of the file that shows what you want the data to look like after the sorting is done?
 

Forum statistics

Threads
1,186,126
Messages
5,956,045
Members
438,231
Latest member
fuzdrum

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top