Copy Range into Specific Row/Cell and avoid overwriting the data

Question

Fairly new to VBA Excel. I want to copy and paste a specific cell[B11 and so on) into a specific cell [E9 and so on] on my target sheet when conditions are met (when C column is equal to No). So far I was able to copy and paste the data on my target sheet. Having trouble when I run the command again. I don't want to overwrite my previous data. How can this be done? `

Private Sub CommandButton1_Click()

    Dim RowGCnt As Long, CShtRow As Long
    Dim LastRow As Long
    Dim CellG As Range

    'paste the first result to the 9th row
    CShtRow = 9
    LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

    For RowGCnt = 11 To LastRow
        If Range("C" & RowGCnt).Value = "No" Then
            MsgBox (CShtRow)
            'Review Criteria
            Worksheets("SHEET1").Range("B" & RowGCnt).Copy
            Worksheets("REPORT").Range("E" & CShtRow).PasteSpecial xlPasteValues
            CShtRow = CShtRow + 1
        End If
    Next RowGCnt

    Application.CutCopyMode = False

End Sub

Answer 1

Untested:

Private Sub CommandButton1_Click()

    Dim shtSrc As Worksheet '<< source sheet
    Dim RowGCnt As Long
    Dim LastRow As Long
    Dim cDest As Range   '<< copy destination

    Set shtSrc = Worksheets("SHEET1")

    'paste the first result to the first open row
    With Worksheets("REPORT")
        Set cDest = .Cells(.Rows.Count, "E").End(xlUp).Offset(1, 0) '<<EDIT
        If cDest.Row < 9 Then Set cDest = .Range("E9")
    End With

    LastRow = shtSrc.Range("A" & shtSrc.Rows.Count).End(xlUp).Row

    For RowGCnt = 11 To LastRow
        If shtSrc.Range("C" & RowGCnt).Value = "No" Then
            cDest.Value = shtSrc.Range("B" & RowGCnt).Value
            Set cDest = cDest.Offset(1, 0)
        End If
    Next RowGCnt

End Sub