Hi All,
Although I've often used this site and found the answers quite easily - this time I've had to post the question....:
So new here at posting - here goes.

I'm building up a macro that takes a newly added name from a staff_list sheet on activating the macro, it adds a new worksheet, names the worksheet as the member of staff and copies in a template page with standard table & formulae on (to record Annual Leave etc).
This seems to work - although one of the cells on the template is coded with :
=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"),1))
to pick up the name from the worksheet and display it in large font at the top of the Leave card.
This works initially but when you tab away from the sheet and back it picks up the name from the last sheet....
the code I need on each worksheet is :
Private Sub cmdSelectWorkbook_Click()
Worksheets(Item).Activate
Cells(2, 2).Calculate
End Sub

this refreshes the sheet on activation.
However I need to send this code from the main Module to the Worksheet as it is created. so I need some way to hold that code in the Module and paste it to the new sheet uder 'Worksheet / Activation'

hope this makes sense

many thanks...
Any advice welcomed.