Hi Rajesh,

try this one.

Code:
Sub kTest()
    
    Dim DestSheets
    Dim SearchValues
    Dim rngData         As Range
    Dim i               As Long
    Dim lCol            As Long
    Dim wksActive       As Worksheet
    
    Const HeaderRow     As Long = 1
    Const SearchCol     As Long = 5
    
    DestSheets = Array("Sheet2", "Sheet3", "Sheet4")                        'adjust the sheet names
    SearchValues = Array("Question 1", "Question 2", "Question 3")          'adjust the strings w.r. to the sheet names
    
    Set wksActive = Worksheets("Sheet1")                                    'adjust the sheet name
    
    Set rngData = wksActive.Cells(HeaderRow, 1).CurrentRegion
    lCol = rngData.Columns.Count
        
    For i = LBound(DestSheets) To UBound(DestSheets)
        With rngData
            .Cells(HeaderRow + 1, lCol + 2).FormulaR1C1 = "=rc[-" & lCol + 2 - SearchCol & "]=""" & SearchValues(i) & """"
            .AdvancedFilter 2, .Cells(HeaderRow, lCol + 2).Resize(2), Worksheets(DestSheets(i)).Range("A1")
        End With
    Next
    
End Sub
Note: Make necessary changes in the code.