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

< Back to Search results

 Category: [Excel]  Demo Available 

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




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.







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
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.


See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets


See also:

How to find account number and add text
How to find a piece of text inside cells in a range and insert a line break on its left
How to find/return first nonblank value in adjacent cell from column with duplicate values
How to use VBA code to find and collect


Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.


stars (0 Reviews)