Name of active sheet

[JenniferMurphy]

An Internet search turned up this expression for obtaining the name of the active sheet.

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

It seems to work, but is there really nothing simpler and more straightforward?

 

[Logit]

.
You could use this macro code :

 Range("A1").Value = ActiveSheet.Name

Here is another approach : https://www.ozgrid.com/VBA/return-sheet-name.htm

 

[Logit]

.
You are welcome. Glad to help.

 

[shg]

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

That returns the name of the sheet containing A1, which may (well) not be the active sheet.

 

[JenniferMurphy]

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

That returns the name of the sheet containing A1, which may (well) not be the active sheet.

I wondered about that. But if the formula is in a cell in Sheet1, wouldn't the "A1" refer to that sheet?

How about this solution?

Public Function ThisSheetName()
ThisSheetName = Application.Caller.Worksheet.Name
End Function

Any problems with that?

PS: I thought I already posted this in response to Logit, but it doesn't seem to be here.

 

[shg]

On Sheet1:
[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td]Sheet1[/td][td]A1: =MID(CELL("filename", A1), FIND("]", CELL("filename", A1)) + 1, 31)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]Sheet1[/td][td]A2: =MID(CELL("filename"), FIND("]", CELL("filename")) + 1, 31)[/td][/tr]
[/table]

On Sheet2:
[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td]Sheet1[/td][td]A1: =Sheet1!A2[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]Sheet2[/td][td]A2: =Sheet1!A2[/td][/tr]
[/table]

Select Sheet1 and press F9; what do you see?

Select Sheet2; what do you see?

Then press F9; what happens?

Absent a sheet reference, the formula returns the activesheet's name, but doesn't update until calculation occurs.

 

[shg]

Sorry, that got messed up.

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td]Sheet1[/td][td]A1: =MID(CELL("filename", A1), FIND("]", CELL("filename", A1)) + 1, 31)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]Sheet1[/td][td]A2: =MID(CELL("filename"), FIND("]", CELL("filename")) + 1, 31)[/td][/tr]
[/table]

Sheet2:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td]Sheet1[/td][td]A1: =Sheet1!A1[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]Sheet2[/td][td]A2: =Sheet1!A2[/td][/tr]
[/table]

Select Sheet1 and now it shows

Sheet1
Sheet2

Press F9 and it shows

Sheet1
Sheet1

Absent a cell reference, the formula returns the active sheet's name, but doesn't update until calculation occurs.

 

[BlueHornet]

Very nice, shg.

For a long time I've used AutoHotKey replacement text values to return the values:

ThisWorksheet enters =RIGHT( CELL( "filename", $A$1), LEN( CELL( "filename", $A$1)) - FIND( "]", CELL( "filename", $A$1)))
ThisWorkbook enters =MID( CELL( "filename", $A$1), FIND( "[", CELL( "filename", $A$1)) + 1, FIND( "]", CELL( "filename", $A$1)) - FIND( "[", CELL( "filename", $A$1)) -1)
ThisPath enters =LEFT( CELL( "filename", $A$1), FIND( "[", CELL( "filename", $A$1)) -1 )
ThisCanonicalFile enters =LEFT( CELL( "filename", $A$1), FIND( "[", CELL( "filename", $A$1)) -1 ) & MID( CELL( "filename", $A$1), FIND( "[", CELL( "filename", $A$1)) + 1, FIND( "]", CELL( "filename", $A$1)) - FIND( "[", CELL( "filename", $A$1)) -1)

(Something similar could be done within Excel in an AutoCorrect, I think.) These are pretty helpful when there's a need to work with INDIRECTs, and especially when worksheet names, file names and/or locations might change from time to time.

Your method for simplifying This Worksheet is neater and shorter than mine, and workable at least until Microsoft reissues Excel with longer Worksheet names than 31 characters.

 

[Logit]

.
If you want to display the sheet name automatically, you can paste this in the Sheet Level Module :

Option Explicit


Private Sub Worksheet_Activate()
    Range("A1").Value = ActiveSheet.Name
End Sub

Drawback to above is that the macro needs to be pasted into every sheet module.



If you want the sheet name to appear when you click a command button, you can paste this into a regular module and connect the button
to this macro :

Sub whatName()
Range("A1").Value = ActiveSheet.Name
End Sub

Drawback to the above is each sheet requires a command button.




You can paste this into the ThisWorkbook Module :

Option Explicit


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    
    Range("A1").Value = ActiveSheet.Name
       
End Sub

Although this macro only requires one pasting into the ThisWorkbook Module the drawback to the above is your workbook will auto open
to Sheet 1 and the sheet name will not display in Cell A1 until you select a different sheet and return to Sheet 1.

 

[shg]

Function SheetName(Optional cell As Range) As String
  If cell Is Nothing Then
    SheetName = Application.Caller.Worksheet.Name
  Else
    SheetName = cell.Worksheet.Name
  End If
End Function