Copy row from one sheet to another, if it isn't already on the first sheet

belle_the_cat

New Member
Joined
Jun 23, 2021
Messages
2
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
  2. MacOS
Hi! I wish I were good at VBA, but sadly this is not the case! I apologize in advance.

I'm working on a workbook that imports several csv files to various sheets, index matches the heck out everything, and the outputs some of the data onto a single page where it's easy to read.

I'm looking for a way to copy a row from sheet 3 and add it to sheet 1, if it is not already on sheet 1. So for any row, if both the invoice number (in row D) and the notes (row F) are already on sheet 1, then don't copy that row over. But if only the invoice number matches, copy over that row.
(The notes section is where people put whatever the issue is with the invoice, which is why I want it to require that both of them match for copying - in the event something was wrong the first time, and then there was another issue on the same invoice, I want the new thing to appear on the list.)

I think I need to use a loop. This is my imagining of what I'm looking for:
( where sheet 1 is the visible sheet, and sheet 3 is the one things are being imported to and matched from)

For sheet 3 row 1, check to see if the value in column D1 matches anything in the D column of sheet 1 (regardless of what row it's in)
if no, copy the row over to sheet 1
if yes, check to see if F1 matches the corresponding F column
if both match, do nothing and move on to the next row
if only the column D matches (but not column F), then copy that row over to sheet 1
and then loop through every row.

Hopefully that makes sense!?
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,437
This code uses the Find method (link) to search for the invoice number.

VBA Code:
Sub Copy_Invoices()
    Dim ws1 As Worksheet, ws3 As Worksheet
    Dim cell As Range, Found As Range
    Dim FirstFound As String
    Dim bCopyInv   As Boolean
    Dim counter    As Long
   
    Set ws1 = Sheets("Sheet1")
    Set ws3 = Sheets("Sheet3")
   
    For Each cell In ws3.Range("D1", ws3.Range("D" & Rows.Count).End(xlUp))
        bCopyInv = True
        Set Found = ws1.Columns("D").Find(What:=cell.Value, _
                                          LookIn:=xlValues, _
                                          LookAt:=xlWhole, _
                                          SearchOrder:=xlByRows, _
                                          SearchDirection:=xlNext, _
                                          MatchCase:=False)
        If Not Found Is Nothing Then
            FirstFound = Found.Address
            Do
                If Found.Offset(0, 2).Value = cell.Offset(0, 2).Value Then
                    bCopyInv = False
                    Exit Do
                End If
                Set Found = ws1.Columns("D").FindNext(after:=Found)
            Loop Until Found.Address = FirstFound
        End If
       
        If bCopyInv Then
            cell.EntireRow.Copy Destination:=ws1.Range("A" & Rows.Count).End(xlUp).Offset(1)
            counter = counter + 1
        End If
       
    Next cell
   
    MsgBox counter & " invoices copied.", vbInformation, "Invoice Copy Complete"
End Sub
 
Solution

belle_the_cat

New Member
Joined
Jun 23, 2021
Messages
2
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
  2. MacOS
I'm so sorry for the delayed reply! This worked amazingly, thank you for all your help!! :)
 
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,152,480
Messages
5,770,330
Members
425,612
Latest member
martinijr

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