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