问题

需要选择工作簿中的所有工作表（工作表编号和名称会有所变化），除了两个名为“Overview”和“Index”的工作表之外（它们恰好是标签列表中最左边的工作表）。

是否有“通用”代码可以做到这一点，而不必为每个工作表命名（除了我不想选择的两个工作表）？

我尝试了以下代码，看看是否可以选择除一个之外的所有工作表：

```vba
Sub Macro1()
Dim i As Long
Sheet1.Select
For i = 2 To ThisWorkbook.Sheets.Count
    If Sheets(i).Name <> "Overview" Then Sheets(i).Select Replace:=False
Next i
End Sub

我得到了：

运行时错误 '1004'

当我点击调试时，它会突出显示 Sheet1.Select 行。

<details>
<summary>英文:</summary>

I need to select all sheets (number and sheet names will vary) in a workbook except two sheets named &quot;Overview&quot; and &quot;Index&quot; (which also happen to be the left-most sheets on the tab list).

Is there &quot;generic&quot; code that can do this without naming each sheet (other than the two sheets that I do not want selected)?

I tried the following code to see if I could select all sheets except one: 

```vba
Sub Macro1()
Dim i As Long
Sheet1.Select
For i = 2 To ThisWorkbook.Sheets.Count
    If Sheets(i).Name &lt;&gt; &quot;Overview&quot; Then Sheets(i).Select Replace:=False
Next i
End Sub

I get:

> run-time error '1004

When I click debug, it highlights the Sheet1.Select line.

答案1

得分: 1

尝试一下：

子宏1()
    Dim iSel As Long, ws As Worksheet
    
    对于每个 ws 在 ThisWorkbook.Worksheets 中
        如果 IsError(Application.Match(ws.Name, _
                   Array("概览", "索引"), 0)) Then
            ws.Select Replace:=(iSel = 0) '仅在选择的第一个工作表中进行替换 
            iSel = iSel + 1 '增加已选择工作表计数
        End If
    下一个 ws
结束子

（假设没有隐藏的工作表）

Sub Macro1()
    Dim iSel As Long, ws As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
        If IsError(Application.Match(ws.Name, _
                   Array("Overview", "Index"), 0)) Then
            ws.Select Replace:=(iSel = 0) 'only Replace for first-selected sheet 
            iSel = iSel + 1 'increment selected sheet count
        End If
    Next ws
End Sub

答案2

得分: 1

以下是代码部分的翻译：

Sub SelectWS()

    Dim WS As Worksheet
    Dim I As Long
    Dim N As Long
    Dim Fnd As Boolean
    Dim Vis As Boolean

    N = 0
    For Each WS In ThisWorkbook.Worksheets

        Vis = (WS.Visible = xlSheetVisible)
        If Vis = False Then N = N + 1

        If WS.Name <> "Overview" And WS.Name <> "Index" And Vis Then
            Fnd = True
            If ActiveSheet.Name = "Overview" Or ActiveSheet.Name = "Index" Then
                WS.Activate
                WS.Select
            Else
                WS.Select (False)
            End If
        End If
    Next WS

    If Not Fnd Then
        MsgBox "No suitable WS found.", vbInformation + vbOKOnly, "Error:"
    ElseIf N > 0 Then
        MsgBox "Found " & N & " hidden Worksheet(s) - not selectable.", vbInformation + vbOKOnly, "Notice:"
    End If

End Sub

Sub SelectWS()

    Dim WS As Worksheet
    Dim I As Long
    Dim N As Long
    Dim Fnd As Boolean
    Dim Vis As Boolean

    N = 0
    For Each WS In ThisWorkbook.Worksheets

        Vis = (WS.Visible = xlSheetVisible)
        If Vis = False Then N = N + 1

        If WS.Name <> "Overview" And WS.Name <> "Index" And Vis Then
            Fnd = True
            If ActiveSheet.Name = "Overview" Or ActiveSheet.Name = "Index" Then
                WS.Activate
                WS.Select
            Else
                WS.Select (False)
            End If
        End If
    Next WS

    If Not Fnd Then
        MsgBox "No suitable WS found.", vbInformation + vbOKOnly, "Error:"
    ElseIf N > 0 Then
        MsgBox "Found " & N & " hidden Worksheet(s) - not selectable.", vbInformation + vbOKOnly, "Notice:"
    End If

End Sub