The following will check to see if value exists, if not then add it to the last row in column.
How I am using it is to find the Prj ID # in Column U on the Planning Tracker and if it does not exist in Column A on the PnL then add it at the end of the column.
But, I also need to include values from seven additional cells on the same row that correspond to the Prj ID
Private Sub CommandButton6_Click()
'**** UPDATES THE PNL REPORT ****
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, rng As Range
Set sh1 = Sheets("Planning Tracker")
Set sh2 = Sheets("PnL")
lr = sh1.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh1.Range("U2:U" & lr)
For Each c In rng
If WorksheetFunction.CountIf(sh2.Range("A:A"), c.Value) = 0 Then
sh2.Range("A" & sh2.Cells(Rows.Count, 1).End(xlUp).Row)(2) = c.Value
End If
Next
Application.ScreenUpdating = True
End Sub
I found this which will execute the multiple columns of copy paste... it only updates the last row and stops. What I need is to add the last section in Bold/Italics to the above code, but cannot figure out how to do it
Public Sub CheckData()
Dim Lastrow As Long
Dim Newrow As Long
Dim i As Long
Dim sh As Worksheet
Application.ScreenUpdating = False
Set sh = Worksheets("Master")
With Worksheets("Reference")
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 4 To Lastrow
If IsError(Application.Match(.Cells(i, "A").Value, sh.Columns("B"), 0)) Then
Newrow = sh.Range("B3").End(xlDown).Row + 1
.Cells(i, "A").Copy sh.Cells(Newrow, "B")
.Cells(i, "B").Copy sh.Cells(Newrow, "C")
.Cells(i, "C").Copy sh.Cells(Newrow, "E")
End If
Next i
End With
Application.ScreenUpdating = True
End Sub
Bookmarks