Find text in column, copy next row and paste in new sheet

This should do it:

Sub CopyNextRow()
    
k = Sheets("SheetWSD").Range("A65000").End(xlUp).Row + 1 'find last row in destination sheet
    
For i = 2 To [a65000].End(xlUp).Row
    Select Case Cells(i, 4)
        Case 7, 11
            Sheets("SheetWSS").Rows(i + 1).Copy Destination:=Sheets("SheetWSD").Cells(k, 1) 'copy next row and past in destination sheet
            k = k + 1 'increase count for next row in destination sheet
    End Select
Next i 'loop until last row in source sheet

End Sub

Hello MikeTiplady,

Welcome to the Forum!

This version is longer than the previous post, but is optimized for speed. Since you have about 10000 rows data, speed becomes important. The macro will also clear the destination sheet each time the macro runs. Both the number of rows and columns used are calculated by the macro so only cells of interest are copied.

Sub Copy711()

  Dim Data() As Variant
  Dim DstRng As Range
  Dim LastCol As Long
  Dim I As Long
  Dim R As Long
  Dim RngEnd As Range
  Dim SrcRng As Range
  
    Set SrcRng = Worksheets("SheetWSS").Range("C1")
    Set DstRng = Worksheets("SheetWSD").Range("A1")
 
      LastCol = SrcRng.Parent.UsedRange.Cells(1, Columns.Count).End(xlToLeft).Column
      Set RngEnd = SrcRng.Parent.Cells(Rows.Count, SrcRng.Column).End(xlUp)
      Set SrcRng = SrcRng.Resize(RngEnd.Row + SrcRng.Row - 1, LastCol)

      ReDim Data(1 To SrcRng.Rows.Count, 1 To 1)
      Data = SrcRng.Columns(1).Value
      DstRng.Parent.UsedRange.ClearContents
      
        For I = 1 To UBound(Data, 1)
          If Data(I, 1) = 7 Or Data(I, 1) = 11 Then
             SrcRng.Cells(I + 1, 1).EntireRow.Copy DstRng.Offset(R, 0)
             R = R + 1
          End If
        Next I
        
End Sub

Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time.
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

To Run the Macro...
To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.