Excel - Copy if condition range is met

December 2016



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.

Related :

This document entitled « Excel - Copy if condition range is met  » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.