Hi Community, 

Thanks in advance for any help or advise. I have built a macro to add a column on a sheet but I now want to copy that sheet and have the formula reference the new sheet not the old sheet. Here is the formula:
--
Sub Button4_Click()

ActiveSheet.Range("F1").Select
ActiveCell.EntireColumn.Insert shift:=x1Down

ActiveSheet.Range("F2").Select
ActiveCell.Formula = "=IF(INDEX(INPUTS!$H$4:$J$79,MATCH('Example Week'!F4,INPUTS!$H$4:$H$79,0),3)="""","""",INDEX(INPUTS!$H$4:$J$79,MATCH('Example Week'!F4,INPUTS!$H$4:$H$79,0),3))"

End Sub
--

When I copy the sheet the above formula is on the macro still has cell F2 reference 'Example Week' instead of 'Example Week (2)'.

I tried replacing 'Example Week'!F4 with: CONCATENATE("'",MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),"!'","F4") but that is what gave me the error.

Any ideas / advice would be much appreciated. Thanks