vba Copy Paste Conditional to put remark 1 2 3 .. etc

[DocAElstein]

Some other final notes to formula solution

The formula from pharmacologist seems to be matching columns A in macro.xlsm to B in 1.xls. The OP wanted columns B in macro.xlsm to I in 1.xls. But I will ignore that for now.

The final formula version of mine will also work if the workbook, 1.xls , is closed.
To verify and get the formula for that, simply close 1.xls and observe the formula changed automatically by Excel

 =IF(OR(AND('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2='C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$E2; 'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2<>'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$F2);AND('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2='C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$F2; 'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2<>'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$E2));IF(ISBLANK(INDEX(macro_xlsmSh1efJ20!B$2:B$7;MATCH('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$B2;macro_xlsmSh1efJ20!$A$2:$A$7)));"";IF(COLUMN(C2)=3;IF(C2="";"";1);INDEX(macro_xlsmSh1efJ20!B$2:B$7;MATCH('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$B2;macro_xlsmSh1efJ20!$A$2:$A$7))+1));C2&"")

_____ Workbook: macro.xlsm ( Using Excel 2007 32 bit )

Row\Col	R	S
2	=IF(OR(AND('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2='C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$E2, 'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2<>'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$F2),AND('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2='C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$F2, 'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2<>'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$E2)),IF(ISBLANK(INDEX(macro_xlsmSh1efJ20!B$2:B$7,MATCH('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))),"",IF(COLUMN(C2)=3,IF(C2="","",1),INDEX(macro_xlsmSh1efJ20!B$2:B$7,MATCH('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))+1)),C2&"")	=IF(OR(AND('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2='C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$E2, 'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2<>'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$F2),AND('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2='C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$F2, 'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2<>'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$E2)),IF(ISBLANK(INDEX(macro_xlsmSh1efJ20!C$2:C$7,MATCH('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))),"",IF(COLUMN(D2)=3,IF(D2="","",1),INDEX(macro_xlsmSh1efJ20!C$2:C$7,MATCH('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))+1)),D2&"")
3	=IF(OR(AND('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D3='C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$E3, 'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D3<>'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$F3),AND('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D3='C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$F3, 'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D3<>'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$E3)),IF(ISBLANK(INDEX(macro_xlsmSh1efJ20!B$2:B$7,MATCH('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$B3,macro_xlsmSh1efJ20!$A$2:$A$7))),"",IF(COLUMN(C3)=3,IF(C3="","",1),INDEX(macro_xlsmSh1efJ20!B$2:B$7,MATCH('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$B3,macro_xlsmSh1efJ20!$A$2:$A$7))+1)),C3&"")	=IF(OR(AND('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D3='C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$E3, 'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D3<>'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$F3),AND('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D3='C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$F3, 'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D3<>'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$E3)),IF(ISBLANK(INDEX(macro_xlsmSh1efJ20!C$2:C$7,MATCH('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$B3,macro_xlsmSh1efJ20!$A$2:$A$7))),"",IF(COLUMN(D3)=3,IF(D3="","",1),INDEX(macro_xlsmSh1efJ20!C$2:C$7,MATCH('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$B3,macro_xlsmSh1efJ20!$A$2:$A$7))+1)),D3&"")

Worksheet: macro_xlsmSh1efJ20


macro.xlsm : https://app.box.com/s/z358r7tbc9hzthi539dlj49jsf4gyg8p
1.xls : https://app.box.com/s/38aoip5xi7018y9syt0xe4g04u95l6xk