Help Adjusting VBA Code Work On New Rows

Hey everyone,

I have attached a sheet to illustrate what I am trying to do, in which I have a macro named 'test'.

I recorded a the macro to refer from Sheet 1 onto the sheet named Master and was hoping to replace the data in Sheet 1 as and when required and run the macro on the next row of the Master sheet and input the new data. However, whenever I run the macro it does the same again on row 2. It seems from the code that it is because each reference has 2 after it but I don't know how to make it dynamic. Code below; any help greatly appreciated.

Sub test()
'
' test Macro
'

'
ActiveCell.FormulaR1C1 = "=Sheet1!R1C"
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:C2"), Type:=xlFillDefault
Range("A2:C2").Select
Range("D2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(R1C,Sheet1!R4C1:R27C2,2,0)"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(R1C[-1],Sheet1!R4C1:R27C3,3,0)"
Range("D2:E2").Select
Selection.Copy
Range("F2,H2,J2,L2,N2,P2,R2,T2,V2,X2").Select
Range("X2").Activate

Range( _
"F2,H2,J2,L2,N2,P2,R2,T2,V2,X2,Z2,AB2,AD2,AF2,AH2,AJ2,AL2,AN2,AP2,AR2,AT2,AV2,AX2" _
).Select
Range("AX2").Activate

ActiveSheet.Paste
End Sub

Hey Yasser,

Thanks for that. It kind of did the same thing, but I have decided on a new strategy for my project and will mark the post as such. Thanks very much.

AV