excel 选择除两张以外的所有图纸

x6h2sr28  于 2023-03-04  发布在  其他
关注(0)|答案(2)|浏览(128)

我需要选择工作簿中的所有工作表(编号和工作表名称将有所不同),但名为"Overview"和"Index"的两个工作表除外(这两个工作表恰好也是选项卡列表中最左侧的工作表)。
是否有"通用"代码可以做到这一点,而无需命名每个工作表(除了我不想选择的两个工作表)?
我尝试了下面的代码,看看是否可以选择除一张以外的所有图纸:

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
当我单击debug时,它突出显示Sheet1.Select行。

fafcakar

fafcakar1#

我有个选择。

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
rryofs0p

rryofs0p2#

试试这个:

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

(假设没有隐藏图纸)

相关问题