Help with looping a copy/paste function in VBA

[arooney88]

Hi everyone, have a situation that I'm wondering how to loop the action to save me some time.

I have 340 assignments, every 3 cells is grouped into a new assignment. Assignment1= Columns B,C,D Assignment2=E,F,G
Assignment3=H,I,J

My issue is that I need to copy/paste the formulas for B5,5C all the way across for 340 assignments but B5 doesn't keep adding the row # because I'm pasting horizontal across and it's a locked reference. My formula in C5 is a date formula that pops the current day whenever an analyst answers a Yes, No, or N/A in B6, E6, H6, etc. C5 works but I'd have to do it by hand for 340 assignments.

What I'm doing now is I built out 20 assignments that are correct order and formulas, I copy/paste them across and then use a macro that finds/replaces to the correct locked cell reference from the Annie sheet, but this takes a lot of manual labor and can lead to mistakes.

I'm a beginner in using VBA for loops but I'm wondering if this is a situation I could start to develop one? Any help on this would be great! Thanks!


[TABLE="width: 700"]
<TBODY>[TR]
[TD][/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]PROC ID
[/TD]
[TD]DATE
[/TD]
[TD]ANALYST
[/TD]
[TD]PROC ID
[/TD]
[TD]DATE
[/TD]
[TD]ANALYST
[/TD]
[TD]PROC ID
[/TD]
[TD]DATE
[/TD]
[TD]ANALYST
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]=Annie'!$D$3
[/TD]
[TD]=IF(B6<>"", IF(B5<>0, NOW(), ""), "")
[/TD]
[TD]Annie C.
[/TD]
[TD]=Annie'!$D$4
[/TD]
[TD]=IF(E6<>"", IF(E5<>0, NOW(), ""), "")
[/TD]
[TD]Annie C.
[/TD]
[TD]=Annie'!$D$5
[/TD]
[TD]=IF(H6<>"", IF(H5<>0, NOW(), ""), "")
[/TD]
[TD]Annie C
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Yes
[/TD]
[TD][/TD]
[TD][/TD]
[TD]No
[/TD]
[TD][/TD]
[TD][/TD]
[TD]N/A
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

 

[arooney88]

Sorry for the bad table that got posted.

The formulas are:

B5=Annie'!$D$3
C5=IF(B6<>"", IF(B5<>0, NOW(), ""), "")