How to get the next sheet name in Excel?

In this article, the user will learn the process of obtaining the name of the next sheet, by using the VBA code in Excel. This article briefs users about the method to open the code area. Write the developed code in the code area, and finally, call the code to generate the required output. The provided VBA code is detailed and accurate. This article also guides users about the possible shortcuts, that can be used to make the task consume less time. For example, to open the code area use the key combination “Alt” +F11, instead of manually selecting the available options and tabs. Obtaining the next sheet name to the current sheet is useful when the user is working with a large number of sheets. This code block will make the user task easy, and less ambiguous.

Example 1: To get the next sheet name in Excel by using the VBA code

Step 1

To understand the process of getting the next sheet name in Excel, click on the “sheet” tabs provided below within the image. The snapshot Excel sheet contains 4 different sheets.

Step 2

To open the VBA code editor, go to the “Developer” tab and then select the “Visual Basic” tab under the code section. Consider the image snapshot for reference −

Step 3

The above step will open a “Microsoft Visual Basic for Applications” dialog box, on the screen. This dialog box contains various options such as a file, edit, window, and many others. This dialog box also contains options to run and debug the provided VBA code.

Another easy method to open code editor is use the key combination of “Alt” + F11.

Step 4

Choose the “Insert” tab, and then click on “Module” option.

Step 5

The above step will open a blank code area.

Type the below-provided code to the code window −

' define function name with string parameter
Function name_of_next_sheet() As String
   ' declare volatile method
   Application.Volatile
   ' calling active sheet and add 1
   ' to go to next sheet
   name_of_next_sheet = ActiveWorkbook.Sheets(ActiveSheet.Index + 1).Name
' end of user defined function
End Function

Snapshot for code area −

Please note that this code can work only when written properly by using the proper code indentation. As VBA code is a syntax-oriented class.

Step 6

Press “Alt” +Q key combination to return to the worksheet. After that go to the G4 cell, and type “=name_of_next_sheet()” to the sheet. After that press the “Enter” key.

Step 7

In the below-provided output screenshot, the user can easily check that the current sheet is “sheet 1”, and after calling the function the name of the next appeared sheet is “sheet 2”.

Conclusion

The above article briefs the user about the way to write the VBA code, to obtain the name of the next sheet. For this example, the learner is using the simplest VBA code module, to perform the required task. All the guided steps are thorough and well-explained. All the steps contain sufficient explanation to support the required task.