First post on this forum but I have found a lot of helpful info here but haven't found a thread that helps me with this particular problem. I have (with great effort as I am relatively new to codes) written the following code which works but deletes formula on dest sheet:
Sub CallMacro()
Range("B1").Sort Key1:=Range("B2"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
j = 2
Set destSheet = Worksheets("Mar17")
Application.EnableEvents = False
destSheet.Range(destSheet.Cells(2, 1), destSheet.Cells(80, 13)).ClearContents
For i = 2 To 80
If Not UCase(Me.Cells(i, 14)) = "X" And IsEmpty(Me.Cells(i, 2).Value) = False Then
Me.Range(Cells(i, 1), Cells(i, 13)).Copy Destination:=destSheet.Range(destSheet.Cells(j, 1), destSheet.Cells(j, 14))
j = j + 1
End If
Next i
Application.EnableEvents = True
End Sub

Problem that I have been struggling with and I think I am missing something simple- I have a formula in column 7. When I run the code it deletes the formula in all rows that data was not transferred to in the dest sheet. One thing I have tried is SpecialCells(xlCellTypeConstants).ClearContents instead of just ClearContents but get error. My data validation remains in dest sheet. Any insight would be greatly appreciated!