Sending VBA code from a Module to Code a worksheet

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.

What if you used this formula instead? It's value doesn't change when other worksheets are activated.

Please Login or Register  to view this content.

Many thanks to Norie & RHCPgergo for such prompt responses,
I liked the idea of modifing the formula in the cell to fix the value - so I tried that, but somehow the error trap stops the VBA on the first mention of 'filename'.
Not sure why this is - as the existing formula has no problems. ( unless they can't exist together on the same sheet - I didn't try that) mmm.
Anyway I had a go with the Workbook level code to refresh all - failed at first but was probaly due to the rouge REM marker on line 3. Once into the 'this workbook' area , worked in a flash. brilliant.
so thanks very much folks .
I'll remember to use Code Tags next time round..!!
cheers