You can use the following methods in VBA to get the name of a specific sheet in an Excel workbook:
Method 1: Get Name of Active Sheet
Function GetSheetName()
GetSheetName = ActiveSheet.Name
End Function
This function will return the name of the active sheet.
Method 2: Get Name of Sheet by Number
Function GetSheetName(N As Integer)
GetSheetName = Sheets(N).Name
End Function
This function will return the name of the nth sheet that you specify.
The following examples show how to use each function in practice with the following Excel workbook that has four sheets:
Example 1: Use VBA to Get Name of Active Sheet
We can create the following function in VBA to get the name of the currently active sheet in an Excel workbook:
Function GetSheetName()
GetSheetName = ActiveSheet.Name
End Function
Suppose the sheet named stats is currently active.
We can type the following formula into cell E1 of this sheet to return the name of the active sheet:
=GetSheetName()
The following screenshot shows how to use this formula in practice:
The function returns the value stats, since this is the name of the sheet that is currently active.
Example 2: Use VBA to Get Name of Sheet by Number
We can create the following function in VBA to get the name of the nth sheet in an Excel workbook:
Function GetSheetName(N As Integer)
GetSheetName = Sheets(N).Name
End Function
Once we’ve created this function, we can then type the following formula into cell E1 of the currently active sheet to return the name of the second sheet in the workbook:
=GetSheetName(2)
The following screenshot shows how to use this formula in practice:
The function returns the value team, since this is the name of the second sheet in the workbook.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA:
VBA: How to Count Number of Sheets in Workbook
VBA: How to Extract Data from Another Workbook
VBA: How to Insert Multiple Rows
