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
Bet_GetWorksheetName = Application.Caller.Parent.Name
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
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
© 2020 Better Solutions Limited. All Rights Reserved. © 2020 Better Solutions Limited TopPrevNext