VBA code to update formula cell reference

[Andymac86]

MSXL2007

I have created a macro to copy the active sheet to the end of the workbook.
Within the worksheet there is the following formula:

=IF(ISNA(INDEX('Equipment Master'!$BW$11:$BW$137,MATCH('Sheet A'!D15,seriallist,FALSE),1)),"0",(INDEX('Equipment Master'!$BW$11:$BW$137,MATCH('Sheet A'!D15,seriallist,FALSE),1)))


The formula above references row BW from the "equipment Master Sheet". For the new sheet i need it to update the formula to row BX from the "Equipment Master" Sheet.

This needs to be automated for each new sheet - step one row to the right.

Can anybody throw a code my way which can help solve my dilemma?

I'm fairly new to VBA so please be gentle.

Thanks