Sub CallMacro to copy data to dest sheet is deleting formula in destination sheet

the code it deletes the formula in all rows that data was not transferred to in the dest sheet

Hi,

In one instance you are clearing the formulas in column 7 in the range A2:M80

destSheet.Range(destSheet.Cells(2, 1), destSheet.Cells(80, 13)).ClearContents

In a second instance, if conditions met, you would have been overwriting even if they weren't cleared (A2:N80)

Me.Range(Cells(i, 1), Cells(i, 13)).Copy Destination:=destSheet.Range(destSheet.Cells(j, 1), destSheet.Cells(j, 14))

Looking at both code lines, you are clearing cells to row 80 (including formulas) but may not be utilizing all of the 80 rows with cleared cells in column G because of your conditional statement. That is why rows that data was not transferred to have no formula.

Both ranges involve clear or overwrite rows in column G. I would suggest to either move you formulas to column 15 or paste in an area beyond column 7. If you want to keep the sheet in the current format, I would delete the line that clears the range and add after the conditional statement a code line that erases the row prior to transferring the data to it. This way you are clearing only the rows that will be written to.

HTH,
Maud

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
            destSheet.Range(destSheet.Cells(j, 1), destSheet.Cells(j, 14)).ClearContents
            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