Excel VBA Video Training/ EXCEL DASHBOARD REPORTS

Return an Excel Worksheet/Sheet Name to a Cell

How to Return an Excel Worksheet/Sheet Name to a Cell via VBA & Custom Function/Formula

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 Instant Buy/Download, 30 Day Money Back Guarantee & Free Excel Help for LIFE!

Got any Excel Questions? Free Excel Help

Return an Excel Worksheet Name to a Cell

In Excel it is possible to use the CELL function/formula and the MID and FIND to return the name of an Excel Worksheet in a Workbook. The formula below shows us how;

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

Where A1 is any non error cell on the Worksheet. If you want the full path of the Excel Workbook, simply use;

=CELL("filename",A1)

It is important to note that the above formulas will only work in a Workbook that has been saved.

Return an Excel Worksheet Name to a Cell VBA

We also use an Excel VBA custom function (user defined function) to return the name of an Excel Worksheet. The big advantage to using a custom VBA function is that we can opt to have the Worksheet name surrounded by both ' and '! e.g 'Sheet1'! This then means we can use the cell as a reference in a formula/function like the INDIRECT function.

Function SheetName(rCell As Range, Optional UseAsRef As Boolean) As String

    Application.Volatile

        If UseAsRef = True Then

            SheetName = "'" & rCell.Parent.Name & "'!"

        Else

            SheetName = rCell.Parent.Name

        End If

End Function

To add the code, open the Visual Basic Editor via Tools>Macro>Visual Basic Editor (Alt+F11) then go to Insert>Module and paste in the code above. Come back into Excel proper and use like shown below to have the Worksheet name returned as 'Sheet1!'

=SheetName(A1,TRUE)

Or as

=SheetName(A1,FALSE) OR =SheetName(A1)

To have the Worksheet name returned as Sheet1

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Excel VBA Video Training/ EXCEL DASHBOARD REPORTS

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft