It cannot accept the code for more than 59 sheets.
What code do i need to use to insert more sheets.
When it change the line the code doesnt work.
Kutools for Excel is a powerful add-in that frees you from performing time-consuming operations in Excel, such as combine sheets quickly, merge cells without losing data, paste to only visible cells, count cells by color and so on. 300+ powerful features / functions for Excel 2019, 2016, 2013, 2010, 2007 or Office 365!
It enables tabbed browsing, editing, and managing of Microsoft Office applications. You can open multiple documents / files in a single tabbed window, such as using the browser IE 8/9/10, Firefox, and Google Chrome. It's compatible with Office 2019, 2016, 2013, 2010, 2007, 2003 or Office 365. Demo
Kutools for Outlook is a powerful add-in that frees you from time-consuming operations which majority of Outlook users has to perform daily! It can save your time from using Microsoft Outlook 2019, 2016, 2013, 2010 or Office 365!
Kutools for Word is a powerful add-in that frees you from time-consuming operations which majority of Word users have to perform daily! It can save your time from using Microsoft Word / Office 2019, 2016, 2013, 2010, 2007, 2003 or Office 365!
Restores the old look and menus of Office 2003 to Microsoft Office 2019, 2016, 2013, 2010, 2007 or Office 365. Don’t lose time in finding commands on the new Ribbon. Easy to deploy to all computers in enterprises and organizations.
Do you want to quickly navigate to a specified worksheet in an Excel workbook? This article will provide a VBA method to open a specified worksheet by double-clicking a certain cell in Excel.
Please do as follows to open a specified worksheet by double-clicking a cell in Excel.
1. Right-click the sheet tab which contains the cell you want to open worksheet by clicking on it. And then click View Code from the context menu. See screenshot:
2. In the opening Microsoft Visual Basic for Applications window, please copy the following VBA code into the Code window.
VBA code: double click cell to open a specified worksheet in Excel
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 'Updated by Extendoffice 20180822 Dim xArray, xAValue As Variant Dim xFNum As Long Dim xStr, xStrRg, xStrSheetName As String xRgArray = Array("A1;Sheet2", "A12;Sheet3", "A4;Sheet4", "A100;Sheet5") On Error Resume Next For xFNum = LBound(xRgArray) To UBound(xRgArray) xStr = "" xStr = xRgArray(xFNum) xAValue = "" xAValue = Split(xStr, ";") xStrRg = "" xStrRg = xAValue(0) xStrSheetName = "" xStrSheetName = xAValue(1) If Not Intersect(Target, Range(xStrRg)) Is Nothing Then Sheets(xStrSheetName).Activate End If Next End Sub
Note: In the VBA code, "A1;Sheet2", "A12;Sheet3", "A4;Sheet4", "A100;Sheet5" mean that doube click cell A1 will open Sheet2, double click A2 will open Sheet3..., please change them based on your needs.
3. Press the Alt + Q keys together to close the Microsoft Visual Basic for Applications window.
From now on, when double clicking cell A1 in current worksheet, the specified worksheet will be activated immediately.