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]

The macro assumes that the data in both workbooks is in a sheet named "Sheet1". If a sheet with that name doesn't exist, you will get the error. Change the sheet names in the code to match yours. Please post your revised code.

I'm confused by what you mean
That line is referring to the sheet with the data that is being copied? So sheets("name of the table") is correct? That's how I have it currently.

Sub Paste()
Workbooks.Open "C:'filepath'\test.xlsx"
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 = Sheets("Master Table")
Set desWS = Workbooks("test.xlsx").Sheets("Sheet1")
lRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
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

I'm sure this is confusing, but the name of the worksheet with the data being manipulated is "Master Table" because I wrote that first lol. test.xlsx is the workbook where I am attempting to test the code to paste the data in the correct place

 

[mumps]

Is the sheet name in the Master named "Sheet1"?

 

[mumps]

What is the name of the sheet in the file that contains the macro? What is the name of the sheet in the test file?

 

[imnotgoodatexcelyet]

What is the name of the sheet in the file that contains the macro? What is the name of the sheet in the test file?

The file that contains the entire history (where we want to paste): test.xlsx
The sheet name in this file is Sheet1

The file that contains the data that needs to be manipulated and where the macro currently resides: WholeMacroAttempt2.xlsm
The sheet name in this file is Master Table

 

[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
    Set srcWS = ThisWorkbook.Sheets("Master Table")
    lRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Workbooks.Open "C:'filepath'\test.xlsx"
    Set desWS = Sheets("Sheet1")
    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

 

[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
    Set srcWS = ThisWorkbook.Sheets("Master Table")
    lRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Workbooks.Open "C:'filepath'\test.xlsx"
    Set desWS = Sheets("Sheet1")
    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

This time I got this error

on this part of the code

val2 = val2 & "|" & v(r, c)

 

[mumps]

I can't reproduce the error. I tested the macro on two dummy workbooks and it worked properly. Could you upload a copy of your two files (de-sensitized if necessary) to a free site such as www.box.com or www.dropbox.com. Once you do that, mark each file for 'Sharing' and you will be given a link to each file that you can post here.

 

[imnotgoodatexcelyet]

I can't reproduce the error. I tested the macro on two dummy workbooks and it worked properly. Could you upload a copy of your two files (de-sensitized if necessary) to a free site such as www.box.com or www.dropbox.com. Once you do that, mark each file for 'Sharing' and you will be given a link to each file that you can post here.

The file where I want the data to be pasted test.xlsx
The file where the data is manipulated WholeMacroAttempt2.xlsm

 

[mumps]

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.

 

[imnotgoodatexcelyet]

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.

They are the results of a formula but I can replace them with something. If I make the cells blank will that fix the issue?