When there are many sheets in your workbook, you will spend a lot of time to go to a specific sheet. Here we will introduce two methods to finish this task.
If there are many sheets in your workbook, you will find it irritating to find and go to your target sheet. In the image below, you can see that there are many sheets in this workbook.
If you want to go to a specific worksheet, you will probably click the arrow in the sheet tab bar to find the sheet. This will cost a lot of time. Hence, below we will demonstrate the steps of the two quick methods.
Method 1: Use Sheet Tab Bar
- Right click the sheet tab bar in the bottom left corner.
- And then you will see this list like the image below shows. In this list, you can directly click the name of the sheet. Thus, you will jump to that sheet immediately.
- If there are more than 16 sheets in the workbook, not all the sheet name will appear in the list. If your sheet is not shown in the list, click the option “More Sheets”.
- In the “Activate” window, drag the scroll bar and choose the sheet that you want to jump to.
- And then click the button “OK”. In addition, you can also double click the sheet name in the window.
You can see that instead of clicking the arrow and find the sheet, using this method can be more convenient.
Method 2: Use VBA Codes
Here you can also use the VBA codes to fulfill the task.
- Press the shortcut keys “Alt +F11” on the keyboard.
- And then right click in the VBA project area.
- In the new menu, move your cursor on the option “Insert”.
- And then choose the “Module” in the submenu. Thus, you have inserted a new module into the file.
- Now copy the following VBA codes into the new module:
Sub JumpToSheet()
Dim FindName As String, FindSheet As Worksheet
FindName = InputBox(prompt:=”Enter the sheet name that you need to find”, Title:=” jump to Specific Sheet “)
For Each FindSheet In ActiveWorkbook.Worksheets
If FindSheet.Name = FindName Then
FindSheet.Activate
Exit Sub
End If
Next
End Sub
- Now press the button “F5” on the keyboard or click the button “Run Sub” in the toolbar to run this procedure.
- After that, you will see an input box pop up. Here input the sheet name into the text box.
- And then click the button “OK” in the window. Therefore, the target sheet will be activated. Here after you click “OK”, you will move back to the interface of the Visual Basic Editor. You can move to the interface of the workbook and check the result.
- If you need to use this VBA codes frequently, you can also add it in the quick access toolbar. In this step, click the small arrow in the toolbar.
- And then choose the option “More Commands” in the list.
- In the “Excel Options” window, choose the “Macros” in the “Choose commands from”.
- And then select the target macro in the list.
- After that, click the button “Add”.
- Now you have added it in the quick access toolbar. Click “OK” in this step.
Hence, the next time if you need to use the VBA codes, you can click the button directly in the quick access toolbar.
Make a Choice between the Two Methods
Actually, both of the two methods are very simple and convenient. You may wonder which method you should choose. In the table below, we have listed all the factors of the two methods, you can refer to it and make a choice.
| Comparison | Use Sheet Tab Bar | Use VBA Codes |
| Advantage | 1. It is easy for you to choose a sheet because you can see all the sheet names in one window.
2. You don’t need to deal with the complex VBA codes by this method. |
1. You can jump to the specific sheet quickly by inputting the name and several clicks.
2. You can also use this method in other workbooks, which proves to be very convenient. |
| Disadvantage | 1. You need to open the “Activate” window every time you need to jump to another sheet.
2. In other workbooks, you will also need to repeat the steps again. And compared with the second method, this is a little troublesome. |
1. If you cannot remember the name of the specific sheet, you cannot use this method.
2. Using VBA macros will make things more complicated. |
From the above table, you will have a better understanding of the two methods. And the next time if you need to realize this task, you can choose one of the methods.
Recovery Tool is a Must for Companies
In most of the companies, Excel is a must for their work. Therefore, there are many essential data and information stored in Excel files. Those files will certainly be victim to virus and malicious hackers. Once data disaster happens, the data and information will all be damaged. Therefore, companies need to prepare a powerful tool at hand. This tool is specially designed and can repair xlsx file and other Excel files easily.
Author Introduction:
Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair doc document and outlook repair software products. For more information visit www.datanumen.com
Leave a Reply