Copy row if criteria is met macro

[Closed]
Report
-
 Ram -
Hello,

I am looking to make a macro that will copy the entire line from sheet 1 to sheet 2 if criteria from column H is met.

Essentially if the cell in Sheet 1 Column H says "Awardee", I'd like all the information from that row to be copied into Sheet 2.
Thanks.

2 replies

Posts
33
Registration date
Sunday June 19, 2016
Status
Contributor
Last seen
November 20, 2016
1
Does this have to be VBA? You could do this with an ARRAY formula or with a helper column.

msg back if this a direction you would be interested in?
I actually originally was using an IF formula at first but the problem was that it would just keep re entering the first row that met the criteria, "awardee".
Posts
33
Registration date
Sunday June 19, 2016
Status
Contributor
Last seen
November 20, 2016
1
Option 1			aaaaa	
Head1 Head2 Head1 Head2
aaaaa 1 aaaaa 1
bbbbb 2 aaaaa 4
ccccc 3
aaaaa 4
bbbbb 5
ccccc 6

D3=IFERROR(INDEX(A:A,SMALL(IF($A$3:$A$8=$D$1,ROW($A$3:$A$8)),ROWS($A$1:A1))),"")
ARRAY entered, using CTRL SHIFT ENTER, not just enter
Then copy down and across as needed

Option 2 using a helper column (which you can hide if needed)
Option 2				aaaaa	
Head1 Head2 Head1 Head2
aaaaa 1 aaaaa 1 aaaaa 1
bbbbb 2 bbbbb 1 aaaaa 4
ccccc 3 ccccc 1
aaaaa 4 aaaaa 2
bbbbb 5 bbbbb 2
ccccc 6 ccccc 2

C3=A3&" "&COUNTIFS($A$3:A3,A3)
copied down
This will create a unique ID for each duplicate that can then be used to extract it

E3=IFERROR(INDEX(A:A,MATCH($E$1&" "&ROWS($A$1:A1),$C:$C,0)),"")
regular formula, copied down and across as needed

Hope that helps?
Sorry I'm rather new to this level of complexity in excel. Anyway you could give an explanation for what each of these options would do?? And what part of the formula has the actual criteria needed to copy a row? And last question is there a way to set it to only copy specific columns (i.e. A, D, F) in that row if the criteria is met in column L?
Thank you.