# Name of active sheet

#### JenniferMurphy

##### Well-known Member
An Internet search turned up this expression for obtaining the name of the active sheet.
Code:
``=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)``
It seems to work, but is there really nothing simpler and more straightforward?

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

#### Logit

##### Well-known Member
Last edited:
• JenniferMurphy

#### Logit

##### Well-known Member
.
You are welcome. Glad to help.

#### shg

##### MrExcel MVP
=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

#### JenniferMurphy

##### Well-known Member
=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?

Code:
``````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

##### MrExcel MVP
On Sheet1:
 A​ B​ 1​ Sheet1 A1: =MID(CELL("filename", A1), FIND("]", CELL("filename", A1)) + 1, 31) 2​ Sheet1 A2: =MID(CELL("filename"), FIND("]", CELL("filename")) + 1, 31)

On Sheet2:
 A​ B​ 1​ Sheet1 A1: =Sheet1!A2 2​ Sheet2 A2: =Sheet1!A2

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.

Last edited:

#### shg

##### MrExcel MVP
Sorry, that got messed up.

 A​ B​ 1​ Sheet1 A1: =MID(CELL("filename", A1), FIND("]", CELL("filename", A1)) + 1, 31) 2​ Sheet1 A2: =MID(CELL("filename"), FIND("]", CELL("filename")) + 1, 31)

Sheet2:

 A​ B​ 1​ Sheet1 A1: =Sheet1!A1 2​ Sheet2 A2: =Sheet1!A2

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.

• JenniferMurphy and BlueHornet

#### BlueHornet

##### Active Member
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.

Last edited:
• JenniferMurphy

#### Logit

##### Well-known Member
.
If you want to display the sheet name automatically, you can paste this in the Sheet Level Module :

Code:
``````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 :

Code:
``````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 :

Code:
``````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.

• JenniferMurphy

#### shg

##### MrExcel MVP
Code:
``````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``````

• JenniferMurphy