How to Finding a value in a sheet and give back related data to another sheet

How to find a value in a sheet and give back related data to another sheet

Requirement:

In a sheet X the user has a cell (J5). The user put there a text. The user would like from the macro to find this text from (J5) in a column B of sheet Y (max 1000 rows to check) and if it finds the right row it copys columns (I:V) of the finded row and pastes it (just values) starting with a row 8 to the columns (B:O) in sheet Y. Macro should give back all the rows with J5.

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1206449-finding-a-value-in-a-sheet-and-give-back-related-data-to-another-sheet

Solution:

Code:

Sub Test()

        Dim sValue As String
        Dim ws As Worksheet, ws1 As Worksheet

        Set ws = Sheets("SheetX") '----> Change sheet name to suit.
        Set ws1 = Sheets("SheetY") '----> Change sheet name to suit.
        sValue = ws.[J5].Value
        
Application.ScreenUpdating = False

With ws1.Range("B1", ws1.Range("B" & ws1.Rows.Count).End(xlUp))
        .AutoFilter 1, sValue
        ws1.Range("I2", ws1.Range("V" & ws1.Rows.Count).End(xlUp)).Copy
        ws.Range("B" & Rows.Count).End(3)(2).PasteSpecial xlValues
        .AutoFilter
End With

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

vcoolio has assumed that you have headings in Row7 of SheetX.

Obtained from the OzGrid Help Forum.

Solution provided by vcoolio.