You can do this with or without VBA.

With VBA

dim aWS as worksheet
for each aWS in activeworkbook.worksheets
aws.cells(row,column).value = aws.name 'You fill in the row and column
value
next aws

Without VBA

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1) )-SEARCH("]",CELL("filename",A1),1))

" wrote:

I am trying to return a value to a cell base on the name of the
worksheet tab. I have around 300 proucts that I need to have a
separate tab for each product. I have used visual basic to name the
tab, now I need to have that same worksheet name appear in a cell.