在excel中列出作業表名稱，同時跳過不需要的作業表

如何在 excel 中生成作業表名稱串列，同時跳過不需要的作業表？

我想列出“費用”、“收入”和“顧問”（綠色標簽）。跳過“模板”、“參考資料 1”和“參考資料 2”（黑色選項卡）。我在使用下面的代碼時收到錯誤。

感謝所有幫助和指導。

Sub List_Sheets()
'
' List_Sheets Macro
'
Dim ws As Worksheet
Dim x As Integer
 
x = 1 'Starting Row
 
For Each ws In Worksheets
    If InStr(ws.Name, "Template") Then 'Skip "Template" also skip "Referance data 1" and "Referance data 2"
        GoTo NextIteration
    Sheets("Summary").Cells(x, 1) = ws.Name 'Starting collunm 1 also know as A
    x = x   1
NextIteration:
Next ws
 
End Sub

uj5u.com熱心網友回復：

這部分：

If InStr(ws.Name, "Template") Then 'Skip "Template" also skip "Referance data 1" and "Referance data 2"
        GoTo NextIteration
    Sheets("Summary").Cells(x, 1) = ws.Name 'Starting collunm 1 also know as A
    x = x   1
NextIteration:

應該變成：

If InStr(ws.Name, "Template") > 0 or _
     InStr(ws.Name, "Reference") > 0  Then GoTo NextIteration
        
     Sheets("Summary").Cells(x, 1) = ws.Name 'Starting collunm 1 also know as A
     x = x   1
NextIteration:

uj5u.com熱心網友回復：

列出某些作業表

Option Explicit

Sub ListSheetsSkip()
    ' The worksheets in the list will be SKIPPED.
    
    Const dSkipSheetsList As String _
        = "Template,Reference Data 1,Reference Data 2"
    Const dfCol As String = "A"
    Const dfRow As Long = 1
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim dws As Worksheet: Set dws = wb.Worksheets("Summary")
    Dim dRow As Long: dRow = dfRow
    
    Dim dSkipSheets() As String: dSkipSheets = Split(dSkipSheetsList, ",")
    
    Dim sws As Worksheet
     
    For Each sws In wb.Worksheets
        If IsError(Application.Match(sws.Name, dSkipSheets, 0)) Then
            dws.Cells(dRow, dfCol).Value = sws.Name
            dRow = dRow   1
        End If
    Next sws
 
    MsgBox "Worksheets found: " & dRow - dfRow, vbInformation
 
End Sub

Sub ListSheetsPick()
    ' The worksheets in the list will be PICKED.
    
    Const dPickSheetsList As String = "Summary,Expenses,Revenue,Advisors"
    Const dfCol As String = "A"
    Const dfRow As Long = 1
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim dws As Worksheet: Set dws = wb.Worksheets("Summary")
    Dim dRow As Long: dRow = dfRow
    
    Dim dPickSheets() As String: dPickSheets = Split(dPickSheetsList, ",")
    
    Dim sws As Worksheet
     
    For Each sws In wb.Worksheets
        If IsNumeric(Application.Match(sws.Name, dPickSheets, 0)) Then
            dws.Cells(dRow, dfCol).Value = sws.Name
            dRow = dRow   1
        End If
    Next sws
 
    MsgBox "Worksheets found: " & dRow - dfRow, vbInformation

End Sub

Sub ListSheetsTabColor()
' The worksheets with a black tab color will be SKIPPED.
    
    Const dfCol As String = "A"
    Const dfRow As Long = 1
    Const NotTabColor As String = "0" ' "0"-Black, "255"-Red, "False"-NoColor
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim dws As Worksheet: Set dws = wb.Worksheets("Summary")
    Dim dRow As Long: dRow = dfRow
    
    Dim sws As Worksheet
     
    For Each sws In wb.Worksheets
        If CStr(sws.Tab.Color) <> NotTabColor Then
            dws.Cells(dRow, dfCol).Value = sws.Name
            dRow = dRow   1
        End If
    Next sws
 
    MsgBox "Worksheets found: " & dRow - dfRow, vbInformation

End Sub

標籤：

上一篇：excel任務欄上的`newsheetbutton` Sign的idMso（名稱）是什么，請看附圖？

下一篇：Excel2016：CHAR(10)問題，即換行