Name of active sheet

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
2,482
Office Version
  1. 365
Platform
  1. Windows
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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
=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.
 
Upvote 0
=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?

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.
 
Upvote 0
On Sheet1:
A​
B​
1​
Sheet1A1: =MID(CELL("filename", A1), FIND("]", CELL("filename", A1)) + 1, 31)
2​
Sheet1A2: =MID(CELL("filename"), FIND("]", CELL("filename")) + 1, 31)

On Sheet2:
A​
B​
1​
Sheet1A1: =Sheet1!A2
2​
Sheet2A2: =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:
Upvote 0
Sorry, that got messed up.

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

Sheet2:

A​
B​
1​
Sheet1A1: =Sheet1!A1
2​
Sheet2A2: =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.
 
Upvote 0
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:
Upvote 0
.
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top