This is my code to check a column is sheet 2 and if the information from sheet 1 is not there, then add the information in columns B through E to the next empty row.
The problem is that it is adding a row after the last valid row I want to add.
Code
Private Sub CommandButton1_Click()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, mg As Range
Set sh1 = Sheets(1) '2020_Other Order Requests'
Set sh2 = Sheets(2) '2020 PO's"'
lr = sh1.Cells(Rows.Count, 1).End(xlUp).Row
Set mg = sh1.Range("B16:B" & lr)
For Each c In mg
If WorksheetFunction.CountIf(sh2.Range("A:A"), c.Value) = 0 Then
sh2.Range("A" & sh2.Cells(Rows.Count, 1).End(xlUp).Row)(2).Resize(1, 4) = c.Resize(1, 4).Value
End If
Next
End Sub
Display More