Worksheet Names

Tab/Display Name

You can refer to your worksheets in VBA code using there programmatic name.
This "name" property can be viewed from the Properties window in the VBE.

Sheet2.Range(A2").Select 

CodeName

The following code always return ThisWorkbook

Debug.Print ActiveWorkbook.CodeName 

This can only be used with ThisWorkbook

Debug.Print ActiveWorkbook.Worksheets(1).CodeName 

Getting the Name of the Worksheet

One way of obtaining the name of the worksheet to appear in one of its cells is to use the Application.Caller property
This property returns a reference to the object that called (or executed) the procedure
When used in a user-defined worksheet function it returns a reference to the cell containing the formula

Public Function BET_GetWorkSheetName() As String 
   Application.Volatile(True) 
   Bet_GetWorksheetName = Application.Caller.Parent.Name 
End Function 

Getting the Name of the previous Worksheet

This worksheet function will always return the name of the previous worksheet.
The worksheet name could then be used in an INDIRECT function.

Public Function BET_PreviousSheet() As String 
Dim isheetcount As Integer 
Dim ssheetname As String 

   Application.Volatile(True) 
   ssheetname = Application.Caller.Parent.Name 
   BET_PreviousSheet = "N/A" 
   For isheetcount = 2 To ActiveWorkbook.Worksheets.Count 
      If ActiveWorkbook.Worksheets(isheetcount).Name = ssheetname Then 
         BET_PreviousSheet = ActiveWorkbook.Worksheets(isheetcount - 1).Name 
      End If 
   Next isheetcount 
End Function