Copy Destination:= is pasting formulas instead of values

[jameslec]

I fell like this is an easy answer. I am copying cells from 1 worksheet to another if a condition is met. My code is working but it is pasting the formulas from the cell instead of the value of the cell. How can I modify this code to paste the values?

Private Sub Worksheet_Change(ByVal Target As Range)


Dim rngCheck As Range
Dim CheckCell As Range
Dim lRow As Long


Set rngCheck = Intersect(Me.Columns("BJ"), Target)


If Not rngCheck Is Nothing Then
For Each CheckCell In rngCheck.Cells
If CheckCell.Value = "Y" Then
With Sheets("Event Log")
lRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
Me.Cells(CheckCell.Row, "A").Copy Destination:=.Cells(lRow, "A")
Me.Cells(CheckCell.Row, "BA").Copy Destination:=.Cells(lRow, "B")
Me.Cells(CheckCell.Row, "BH").Copy Destination:=.Cells(lRow, "E")
Me.Cells(CheckCell.Row, "BB").Copy Destination:=.Cells(lRow, "F")
End With
End If
Next CheckCell
End If


End Sub

 

[L. Howard]

Snakehips,

Okay, good. He is off and running with a solution.

Regards,
Howard