How to find a value in a sheet and give back related data to another sheet
Category: [Excel] Demo Available
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.
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.
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.