Copy if condition range is met Solved/Closed

Question

hey guys  

i tried hard, altered other peoples codes but i cant figure it out for the life of me 

all im trying to do is copy 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 

all i want is if the names listed on Dom's sheet that appear in " insert" sheet is to have that row  copied to row 75 and above in Dom's sheet  

Thanks

Configuration: Windows XP / Safari 535.1

venkat1926 · Answer

"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

venkat1926 · Answer

there are no column heading in either of the sheets . try this modified macro

Sub test()
Dim cfind As Range, c As Range, x As String, dest As Range, j As Long, r As Range
j = 1
With Worksheets("dom")
For Each c In Range(.Range("B4"), .Range("B4").End(xlDown))
x = c.Value
With Worksheets("insert")
Set r = Range(.Range("g1"), .Range("G1").End(xlDown))
Set cfind = r.Cells.Find(what:=x, lookat:=xlWhole)
If Not cfind Is Nothing Then
cfind.EntireRow.Copy
Else
GoTo nextc
End If
End With
.Range("A75").Offset(j, 0).PasteSpecial
j = j + 1
nextc:
Next c
End With
End Sub


Sub undo()
With Worksheets("dom")
Range(.Range("A75"), .Cells(Rows.Count, "A")).EntireRow.Delete
End With

Copy if condition range is met

2 responses

Similar discussions

Subscribe To Our Newsletter!