Copy if condition range is met
Solved/Closed
Related:
- Copy if condition range is met
- Excel if range of cells contains specific text then return value ✓ - Excel Forum
- Apple airtag range - Guide
- Selection.autofill destination:=range ✓ - Excel Forum
- Available memory less than 15mb condition zero - Guide
- Excel add row if condition met - Excel Forum
2 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Oct 25, 2011 at 05:29 AM
Oct 25, 2011 at 05:29 AM
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
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Oct 25, 2011 at 12:31 AM
Oct 25, 2011 at 12:31 AM
"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.
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
Awsome that worked :)...
the only thing is, column G sheet "insert" goes from G14 to G3500, In Dom sheet there is nothing beyond A89, but from A76 - A89 copied over fine
Extract:
"Dom"
B4 - james K
B5 - ryan M
B6 - kelly S
B7 - vince R
"insert"
Sent Customer Customer segment Replied NPS devilery on time Staff Member
02-Oct-11 269583 urgent 02-Oct-11 8 yes james K
03-Oct-11 269583 urgent 04-Oct-11 8 no ryan M
thanks youor soo much fo looking into this and promtness
the only thing is, column G sheet "insert" goes from G14 to G3500, In Dom sheet there is nothing beyond A89, but from A76 - A89 copied over fine
Extract:
"Dom"
B4 - james K
B5 - ryan M
B6 - kelly S
B7 - vince R
"insert"
Sent Customer Customer segment Replied NPS devilery on time Staff Member
02-Oct-11 269583 urgent 02-Oct-11 8 yes james K
03-Oct-11 269583 urgent 04-Oct-11 8 no ryan M
thanks youor soo much fo looking into this and promtness
Oct 26, 2011 at 12:32 AM