Issue
I want to copy data from sheet to another if the entry range is met on another sheet
All my data is in sheet called " insert" row ,G sheet "insert" has the names of people
In sheet name " Dom " range B4:b17 has the list of names of that team
If the names listed on Dom's sheet that appear in " insert" sheet, I want to have that row copied to row 75 and above in Dom's sheet
Solution
"Row ,G sheet "insert" has the names of people "
G cannot be a row only a column assuming in g column I had to make a sample sheet
Insert sheet will look like this from column A to H
col.G col H
name data a 1 s 2 d 3 f 4 g 5 h 6
Dom sheet will be like this
col B row4 a d g k w r y u z c b m n p
I wrote macro run the macro and see whether this is what you want. It would have been more helpful had you given a small extract of insert and dom sheets. If you want and can modify the macro to suit your data.
BETTER SAVE THE ORIGINAL FILE SAFELY SOMEWHERE FOR RETRIEVAL IF SOMETHING GOES WRONG.
Sub test() Dim cfind As Range, c As Range, x As String, dest As Range, j As Long j = 1 With Worksheets("dom") For Each c In .Range("B4:B17") x = c.Value With Worksheets("insert").UsedRange Set cfind = .Cells.Find(what:=x, lookat:=xlWhole) If Not cfind Is Nothing Then cfind.EntireRow.Copy End With .Range("A75").Offset(j, 0).PasteSpecial j = j + 1 Next c End With End Sub Sub undo() With Worksheets("dom") Range(.Range("A75"), .Cells(Rows.Count, "A")).EntireRow.Delete End With End Sub
Thanks to Venkat for this tip.
