如何调整自动生成工作表菜单的VBA代码，从第12张工作表开始执行？

Got it! Let's adjust your existing VBA code to skip the first 11 fixed worksheets and start building your menu from the 12th one. Here are two improved versions of your code, along with explanations of the key changes:

Version 1: Minor Tweaks to Your Original Code #

This keeps the structure you're familiar with, while adding the logic to skip the first 11 sheets:

Dim objSheet As Worksheet
Dim startRow As Integer ' Makes it easier to adjust the starting row later

' Set your fixed starting point
Worksheets("General Information").Activate
startRow = 14
Range("W" & startRow).Select

For Each objSheet In ActiveWorkbook.Worksheets
    ' Only process worksheets starting from the 12th one (index starts at 1)
    If objSheet.Index >= 12 Then
        ActiveCell.Hyperlinks.Add Anchor:=Selection, _
            Address:="", _
            SubAddress:="'" & objSheet.Name & "'!A1", _
            TextToDisplay:=objSheet.Name
        
        ' Move to the next row for the next hyperlink
        startRow = startRow + 1
        Range("W" & startRow).Select
    End If
Next objSheet

' Auto-fit column W once after generating all links (more efficient than doing it in the loop)
Columns("W:W").AutoFit

Key Changes: #

• Added a startRow variable to make adjusting your starting cell (W14) simpler later on.
• Replaced the original name-check condition with objSheet.Index >= 12—since worksheet indexes start at 1, this skips the first 11 sheets directly.
• Moved the column auto-fit outside the loop: no need to run it every time you add a link; doing it once at the end is faster.

Version 2: More Efficient (No Select/Activate) #

For better performance and stability, it's best to avoid using Select and Activate in VBA. Here's a cleaner version that works without them:

Dim objSheet As Worksheet
Dim wsMenu As Worksheet
Dim startRow As Integer

' Directly reference your menu worksheet instead of activating it
Set wsMenu = ThisWorkbook.Worksheets("General Information")
startRow = 14

For Each objSheet In ThisWorkbook.Worksheets
    If objSheet.Index >= 12 Then
        ' Add the hyperlink directly to the target cell
        wsMenu.Hyperlinks.Add Anchor:=wsMenu.Range("W" & startRow), _
            Address:="", _
            SubAddress:="'" & objSheet.Name & "'!A1", _
            TextToDisplay:=objSheet.Name
        
        ' Increment the row counter for the next entry
        startRow = startRow + 1
    End If
Next objSheet

' Auto-fit the column once at the end
wsMenu.Columns("W:W").AutoFit

Why This is Better: #

• No activating or selecting cells means the code runs faster and won't be interrupted if you click on another sheet while it's running.
• Directly referencing the worksheet (wsMenu) makes the code more readable and less prone to errors.

内容的提问来源于stack exchange，提问作者Erik Vonk