WORKSHEETNAME

Returns the name of the worksheet in a given position.

REMARKS

'iSheetNo - The number of the worksheet you want the name of.

Public Function WORKSHEETNAME(Optional ByVal iSheetNo As Integer = -1) As String 

   If iSheetNo = -1 Then 
      WORKSHEETNAME = Application.ActiveSheet.Name 
   Else 
      WORKSHEETNAME = Application.Sheets(iSheetNo).Name 
   End If 

End Function 

You can also get the name of the worksheet by using the CELL function.
You can use the following formula

=MID(CELL("filename",A1)), FIND("]",CELL("filename",A!)) + 1, 1000) 

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,99
In Power Utility Pak this function is called SHEETNAME
Parameters:
reference = Optional, a cell on the sheet that you want to get the name from. If omitted the name of the current sheet is returned.

For instructions on how to add this function to a workbook refer to the page under Inserting Functions