H

#### haileybury

2. If value not found in sheet2 then I want to copy the value from sheet1 and

paste it at the bottom of the list of values in sheet2. What is not working

is the statement that I am using to copy the row from sheet 1 and pasting it

to sheet2

Cel.EntireRow.Copy destination:=Sht2.Cells(r, 1)

Following is the code

Private Sub CommandButton2_Click()

Dim r As Long 'Last row of data on Sheet 1 (row number)

Dim c As Range 'Range/counter

Dim Rng As Range 'Row 2 to row 'r' (above) as counter

Dim Cel As Range 'Cell value to find from Sheet1 on Sht2

Dim Sht1 As Worksheet 'Sheet1

Dim Sht2 As Worksheet 'Sheet2

Dim Sht3 As Worksheet 'Sheet3

Set Sht1 = Sheets("sheet1")

Set Sht2 = Sheets("sheet2")

Set Sht3 = Sheets("sheet3")

'Get last row Sheet 1, Column a

r = Sht1.Range("a65536").End(xlUp).Row

'Start with Sheet1

Sht1.Activate

'Set 'counter' as first row of data to last row of data (Col a)

Set Rng = Range(Cells(2, 1), Cells(r, 1))

'For cells first to last (counter)

For Each Cel In Rng

'find first Sheet1.Cell.Value in sheet 2

With Sht2.Cells

Set c = .Find(Cel, LookIn:=xlValues)

If c <> "" Then GoTo phred

r = Sht2.Range("a65536").End(xlUp).Row + 1

Cel.EntireRow.Copy destination:=Sht2.Cells(r, 1) ' this is not

working...

End With

phred:

MsgBox "went to phred"

Next

End Sub

any tips or recommendations is much appreciated.