Excel function to list worksheet names

Ever wanted to list all the tabs in a workbook or reference a specific sheet by its placing, look no further.....

This funky little custom VBA function can be used simply as a contents sheet, This is a particularily useful tool tool when used with the indirect function, it will allow you to soft-link to any cell within your worksheet.

There are a few things you should note though, it sometimes won't update after a sheet move/copy/delete/rename so a good old calc all - Ctrl-Alt-F9 won't go amiss.

Function WorksheetName(SheetNo As Integer)
	    'Function to return worksheet name for its given number order
	    'Copyright Marty Childs 2008
	    'Usage =WorksheetName(1) will return "Sheet1" in a blank workbook
	    WorksheetName = ThisWorkbook.Sheets(SheetNo).Name
End Function

Follow these instructions to create the macro for Excel 2003 or 2007. Also the function needs to be saved into workbook itself so it is available to everyone and for 2007 users and above dont forget to save the file as a macro-enabled workbook.

Search Posts

Back to top