VBA copy row to new sheet if cell value isn't found in range

[Magoosball]

Hello I'm trying to write a macro that will copy an entire row from sheet2 and paste it to the bottom of sheet1 if the cell value in column f sheet 2 doesn't exist at all in column f on sheet 1.

For example if column f sheet 2 = 123456 and 123456 doesn't exist anywhere in sheet1 column f then copy the entire row from sheet2 and paste it to the bottom of sheet1.

This will have to exist in a loop for all rows on sheet 2.

Thanks in advance!

 

[Fluff]

Re: VBA copy row to new sheet if cell value isn't found in rance

Untested, but try

Sub Magoo()
   Dim Cl As Range, Rng As Range
   Dim Ws1 As Worksheet, Ws2 As Worksheet
   
   Set Ws1 = Sheets("sheet1")
   Set Ws2 = Sheets("sheet2")
   With CreateObject("scripting.dictionary")
      For Each Cl In Ws1.Range("F2", Ws1.Range("F" & Rows.Count).End(xlUp))
         .Item(Cl.Value) = Empty
      Next Cl
      For Each Cl In Ws2.Range("F2", Ws2.Range("F" & Rows.Count).End(xlUp))
         If Not .Exists(Cl.Value) Then
            If Rng Is Nothing Then Set Rng = Cl Else Set Rng = Union(Rng, Cl)
         End If
      Next Cl
   End With
   If Not Rng Is Nothing Then
      Rng.EntireRow.Copy Ws1.Range("A" & Rows.Count).End(xlUp).Offset(1)
   End If
End Sub

 

[Magoosball]

Re: VBA copy row to new sheet if cell value isn't found in rance

Thank you Fluff! Like always it works perfect!

 

[Fluff]

Re: VBA copy row to new sheet if cell value isn't found in rance

Glad to help & thanks for the feedback

 

[earth1899]

Re: VBA copy row to new sheet if cell value isn't found in rance

Glad to help & thanks for the feedback

Hi!

I have found this code very useful for a project I'm working on. However, I have a couple of adjustments I'm not sure how to make. For one, when I copy the entire row, it brings over the *entire* row - blank cells all the way to the end of the workbook - which makes scrolling through the destination sheet kind of inconvenient with all the blank cells. Is there a way to copy the row with only data? Right now, I am just doing rng.EntireRow.SpecialCells(xlCellTypeVisible).Copy since I have a hidden column that I don't want copied. This is also part of an actual table (list object).

 

[Fluff]

As this is significantly different, you need to start a thread of your own. Thanks

 

[stingone]

Re: VBA copy row to new sheet if cell value isn't found in rance

Untested, but try

Sub Magoo()
   Dim Cl As Range, Rng As Range
   Dim Ws1 As Worksheet, Ws2 As Worksheet
  
   Set Ws1 = Sheets("sheet1")
   Set Ws2 = Sheets("sheet2")
   With CreateObject("scripting.dictionary")
      For Each Cl In Ws1.Range("F2", Ws1.Range("F" & Rows.Count).End(xlUp))
         .Item(Cl.Value) = Empty
      Next Cl
      For Each Cl In Ws2.Range("F2", Ws2.Range("F" & Rows.Count).End(xlUp))
         If Not .Exists(Cl.Value) Then
            If Rng Is Nothing Then Set Rng = Cl Else Set Rng = Union(Rng, Cl)
         End If
      Next Cl
   End With
   If Not Rng Is Nothing Then
      Rng.EntireRow.Copy Ws1.Range("A" & Rows.Count).End(xlUp).Offset(1)
   End If
End Sub

How can you copy and entire row and paste only the values? with this code it also copies the formules.

 

[Fluff]

Please start a thread of your own for this question.