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!
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
11,718
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of each of your relevant sheets.
Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing'
and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns, sheets and workbooks using a few examples from your data (de-sensitized if necessary).
 

imnotgoodatexcelyet

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

The top row is there only for detail and won't be in the final version of the manipulated data. The second row is the first row from the data after it gets manipulated. Basically I want to find this exact row (which will differ every time this macro is run) in a separate workbook (Master) and then paste every row of the manipulated data into the master workbook. So this row, and all the subsequent rows (amount will differ every time the macro is run), would get pasted over the duplicate in the master.
Does that make sense?
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
11,718
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
It is hard to work with a picture. Please see my instructions in Post #2 to post screenshots (not pictures) of each of your relevant sheets from each workbook, preferably with a little more data.
 

imnotgoodatexcelyet

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

ADVERTISEMENT

Here is the mini sheet. The data in the Master looks exactly the same. I want to find the exact copy of the first row in the Master and then paste all of these rows in the master starting at the location of the duplicate
WholeMacroAttempt2.xlsm
ABCDEFGHIJ
1nameOut445712:21 PM1095to$0.000:00:47Normal incoming or outgoing phone callname
2nameOut445712:18 PM1095to$0.000:01:12Normal incoming or outgoing phone callname
3nameOut445712:18 PM1095to$0.000:00:22Normal incoming or outgoing phone callname
4nameOut4457111:38 AM1095to$0.000:00:58Normal incoming or outgoing phone callname
5nameOut445718:46 AM1095to$0.000:03:05Normal incoming or outgoing phone callname
6nameOut445718:36 AM1095to$0.000:01:21Normal incoming or outgoing phone callname
Master Table
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
11,718
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
To clarify:
Do you want to check if only the first row in the mini sheet exists in the Master or if each row in the mini sheet exists in the Master? If they exist in the Master, do you want to copy each row from the mini sheet at the location of the duplicate in the Master? This is a little confusing. If the rows match, why would you want to copy and paste them if they are the same? Please explain step by step in more detail using a few examples from your data. Also post the data in the Master.
 

imnotgoodatexcelyet

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

ADVERTISEMENT

To clarify:
Do you want to check if only the first row in the mini sheet exists in the Master or if each row in the mini sheet exists in the Master? If they exist in the Master, do you want to copy each row from the mini sheet at the location of the duplicate in the Master? This is a little confusing. If the rows match, why would you want to copy and paste them if they are the same? Please explain step by step in more detail using a few examples from your data. Also post the data in the Master.
I only need to find a match of the first row because that will tell me where to start the paste
I need to copy every row from the manipulated data (between 1000-3000 rows). We always get the previous day's data when we run our report just to ensure that we don't miss any data, that's just how we do it. This results in the duplicates which is why I want to match the first row so I know where to paste, hence eliminating the duplicates

1. We run the report. This grabs data from today as well as yesterday
2. We manipulate the data and then sort by date and user (columns C and A) in descending order
3. We paste the entire manipulated data over the beginning of the previous day's data in the master. When we paste over the data from the previous day it ensures we don't have duplicates while adding the data from today. We manipulate the data to be formatted in the exact same way as the master

The master will look exactly like the manipulated data for the first xxxx amount of rows since we grab the previous day as well
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
11,718
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
Make sure that both workbooks are open. Change the sheet names (in red) and the workbook name (in blue) to suit your needs. Place the macro in the workbook with the mini sheet.
Rich (BB 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 = Sheets("Sheet1")
    Set desWS = Workbooks("Master.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
 

imnotgoodatexcelyet

New Member
Joined
Jan 17, 2022
Messages
27
Office Version
  1. 2019
Platform
  1. Windows
Make sure that both workbooks are open. Change the sheet names (in red) and the workbook name (in blue) to suit your needs. Place the macro in the workbook with the mini sheet.
Rich (BB 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 = Sheets("Sheet1")
    Set desWS = Workbooks("Master.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 get this error
1642446260918.png

On this line
Set srcWS = Sheets("Sheet1")


Also, I don't know if this matters, but I added a script to open the master workbook at the beginning of the sub.

I really appreciate all of your help
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
11,718
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,605
Messages
5,832,677
Members
430,153
Latest member
Javid_P

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