Here is a way to get the worksheet name into any cell on the worksheet:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

The instance of the CELL function in this formula returns the full name of the worksheet, including the filename and file path. It would normally return something like this:
C:\Documents and Settings\ExcelAssist\Documents\[test.xls]Sheet1

Use the FIND function to strip out everything except the tab name.