isempty function is not working to show value if cell is empty

Try:

Sub report()
    Application.ScreenUpdating = False
    Dim LastRow As Long, status As Range
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each status In Range("E2:E" & LastRow)
        Select Case status.Value
            Case "Completed"
                If status.Offset(, 6) <> "" Then
                    status.Offset(, 5) = "Yes"
                ElseIf status.Offset(, 6) = "" Then
                    status.Offset(, 5) = "No"
                End If
            Case "Open"
                If status.Offset(, 6) = "" Then
                    status.Offset(, 5) = "Open"
                End If
            Case "Cancelled"
                If status.Offset(, 6) = "" Then
                    status.Offset(, 5) = "Cancelled"
                End If
        End Select
    Next status
    Application.ScreenUpdating = True
End Sub

Do you want this to happen only when column J in Sheet1 ="Yes"?

When you say

run automatically after i have pasted the data

do you mean you will be pasting data in column E (Status)? In other words, will you be pasting "Completed", "Open" and "Cancelled" into column E? There is another approach that you might consider. You could put a drop down list in column E where you could select "Completed", "Open" or "Cancelled" and upon selection, the macro would run automatically. Would that work for you?

Which of the two approaches do you want to use, the pasting approach or the drop down approach?

I think we will have to use only the "paste" option because if there are drop downs in column E, they will be deleted when you paste in the data. Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("E:E")) Is Nothing Then Exit Sub
    Dim status As Range
    For Each status In Target
        Select Case status.Value
            Case "Completed"
                If status.Offset(, 6) <> "" Then
                    status.Offset(, 5) = "Yes"
                ElseIf status.Offset(, 6) = "" Then
                    status.Offset(, 5) = "No"
                End If
            Case "Open"
                If status.Offset(, 6) = "" Then
                    status.Offset(, 5) = "Open"
                End If
            Case "Cancelled"
                If status.Offset(, 6) = "" Then
                    status.Offset(, 5) = "Cancelled"
                End If
        End Select
    Next status
End Sub

I forgot to mention that the macro must go into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Paste the data into column E.