Excel macro to find a cell string in a column and copy all data below it and paste into another sheet

Question

I have a scenario where I need to copy all data in a columnA below a specific cell which has certain set string value. I tried below code but the data below the string in columnA is not getting copied and not pasting to another sheet. Any help is appreciated.

Sub Macro11()
 Dim strSearch As String
 Dim fVal As Range
 Dim lastrow As Long
 Dim wk As Workbook

'Set the value you want to search
strSearch = "*Question*"

Set wk = ThisWorkbook

With wk.Worksheets("Sheet1")
         'Find string on column A
    Set fVal = .Columns("A:A").Find(strSearch, LookIn:=xlValues, lookat:=xlWhole)
    
    If fVal Is Nothing Then
        'Not found
        MsgBox "Not Found"
    Else
    'Found
        MsgBox "Found at: " & fVal.Address
       .Range("A:A" & (fVal.Row)).Copy
        Sheets("Sheet2").Activate
        Sheets("Sheet2").Range("B1").PasteSpecial xlPasteValues
    End If
 End With
End Sub

Answer 1

• Assign values is more efficient than Copy/PasteSpecial
• The syntax of .Range("A:A" & (fVal.Row)) is incorrect; it should be something like .Range("A10:A25").
• If you don't need to copy cell fVal, chang to .Range(fVal.Offset(1), .Cells(lastrow, "A"))

Microsoft documentation:

Range object (Excel)

Range.Resize property (Excel)

Sub Macro11()
    Dim strSearch As String
    Dim fVal As Range
    Dim lastrow As Long
    Dim wk As Workbook
    'Set the value you want to search
    strSearch = "*Question*"
    Set wk = ThisWorkbook
    With wk.Worksheets("Sheet1")
        'Find string on column A
        Set fVal = .Columns("A:A").Find(strSearch, LookIn:=xlValues, lookat:=xlWhole)
        If fVal Is Nothing Then
            'Not found
            MsgBox "Not Found"
        Else
            MsgBox "Found at: " & fVal.Address
            lastrow = .Cells(.Rows.Count, "A").End(xlup).row
            With .Range(fVal, .Cells(lastrow, "A"))
                wk.Sheets("Sheet2").Range("B1").Resize(.Cells.Count).Value = .Value
            End With
        End If
    End With
End Sub