Copy if condition range is met
Solved/Closed
Related:
- Copy if condition range is met
- Apple airtag range - Guide
- Air condition lidl - Home - Apps & Sites
- Microsoft office 2010 gratis downloaden met product key - Download - Office suites
- Excel conditional formatting if another cell contains specific text ✓ - Excel Forum
- If function with date range - Guide
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