如何遍历除TOC以外的工作表 [英] How to Loop through Worksheets Except TOC

问题描述

我正在尝试循环一个将在除"TOC"表之外的所有工作表中运行的宏.
-每个工作表都将具有一个已创建，已格式化并具有公式的表格.

但是，宏试图从"TOC"表开始，然后导致错误.

我目前有:

  Sub BrandRank_()Dim wb as ThisWorkbook昏暗的工作表昏暗的TableName作为字符串Dim LstObj作为ListObjects昏暗的LastRow只要长对于ThisWorkbook.Worksheets中的每个ws选择案例名称大小写为="TOC"其他情况与ws‘插入表格TableName ="MyTable"Range("A3").CurrentRegion.SelectActiveSheet.ListObjects.Add(xlSrcRange，Selection.CurrentRegion，xlYes).Name = TableName将过滤条件应用于所有品牌的$份额(从最小到最小)ws.AutoFilter.Sort.SortFields.Clearws.AutoFilter.Sort.SortFields.Add2键:= Range("C3")，SortOn:= xlSortOnValues，顺序:= xlDescendingws.AutoFilter.ApplyFilterActiveSheet.ListObjects("Table1").ShowAutoFilterDropDown = False'更多表格格式代码结束于结束选择下一个ws结束Sub` 

在下面进行更新以反映塞缪尔·埃弗森的建议:

在我的场景中，Sheet1(TOC)是我要跳过的工作表.所以，我应该这样写代码吗?

  Sub LoopThroughWorkSheets_()Dim wb as ThisWorkbook昏暗的工作表昏暗的TableName作为字符串Dim LstObj作为ListObjects设置ws = ThisWorkbook.Sheets("Sheet2")对于ThisWorkbook.Worksheets中的每个ws选择案例名称大小写为="TOC"其他情况与ws'插入表，数据从A列第3行开始TableName ="MyTable".Range("A3").CurrentRegion.Select.ListObjects.Add(xlSrcRange，Selection.CurrentRegion，xlYes).Name = TableName'将过滤器应用于C3(从最小到最小).AutoFilter.Sort.SortFields.Clear.AutoFilter.Sort.SortFields.Add2键:= Range("C3")，SortOn:= xlSortOnValues，顺序:= xlDescending.AutoFilter.ApplyFilter.ListObjects("TableName").ShowAutoFilterDropDown = False'将公式添加到F4，然后复制到F中的最后一行(使用A列作为LastRowData参考).Range("F4").Formula ="=(E4/(E4-G4))-1".Range("F4").NumberFormat ="0.0％".Range("F4").AutoFill目的地:= Range("F4:F"& Range("A"& Rows.Count).End(xlUp).Row)'更新E列中的数字格式G.Columns("E").NumberFormat ="$#，## 0".Columns("G").NumberFormat ="$#，## 0"结束于结束选择下一个ws结束子 

解决方案

正如注释中所指出的，您没有在 With ws 语句中使用.我在

如果我们基于该屏幕快照中的工作簿执行代码，则结果将到达我们假定的位置- Sheet1 单元格 A1 和 A2 .但是，如果我们在 Sheet3 处于活动状态时运行代码，则代码会将值输出到 Sheet3 单元格 A1 和 A2

这是因为在我们的 With 语句中，我们没有将 ws 工作表用作 Range 属性的对象限定符.

range属性可以同时应用于 Application 和 Worksheet 对象，并且每个

如果您需要将某些语句应用于其他工作表但在 With 块中执行，则最好使用对工作簿的显式引用，而不是> ActiveSheet -这将有助于解释更多-(如何避免在vba中使用select)

因此，如果您代码中的 ListObjects 实际上位于 Sheets("TOC")上，而不是循环所指的 ws 上，将 ActiveSheet.ListObjects ... 和 ActiveSheet.ListObjects("Table1")... 更改为:

  wb.Sheets("TOC").ListObjects ...wb.Sheets("TOC").ListObjects("Table1")... 

---

在您的 For Each ... Next 循环中，您的变量 ws 在这种情况下表示一个对象-特别是工作表.变量 ws 在每次迭代时都会分配给下一个对象，因此您不需要将工作表对象设置为变量，而不必依赖于循环.

在上面的示例中，我有3张纸.下面的两个代码块将实现相同的结果，一个通过 For Each ... Next 循环，另一个不通过.

对于每种...下一种方法:

  Sub ForEachExample()昏暗的工作表对于ThisWorkbook.Worksheets中的每个ws调试打印ws.Name下一个ws结束子 

无循环方法:

  Sub NoLoopExample()昏暗的工作表设置ws = ThisWorkbook.Worksheets(1)'与Sheets(1)或Sheets("Sheet1")相同调试打印ws.Name设置ws = ThisWorkbook.Sheets(2)'与Worksheets(2)或Sheets("Sheet2")相同调试打印ws.Name设置ws = ThisWorkbook.Sheets("Sheet3")'与Worksheets(3)或Sheets(3)相同调试打印ws.Name结束子 

它们两个都将以下内容输出到VBE的立即窗口中:

  Sheet1工作表2工作表3 

在更新的代码中，您将 ws 设置为 ThisWorkbook.Sheets("Sheet2").

1. 您的工作簿中将需要一个名为"Sheet2" 的工作表，否则您将收到运行时错误9:下标超出范围错误.
2. 代码中的下一行是 For Each ... Next 语句，该语句会将 ws 设置为 Worksheets 中的第一张工作表集合，而不考虑上一行代码.

我要指出的是，这行代码在当前过程中是多余的，并且取决于工作表的名称，可能会导致运行时错误.

请参见对于每个...下一个语句文档.

---

使用您的 Select Case 语句，第一行 Select Case ws.Names 应该抛出 Compile Error:Type mismatch . ws.Names 根据对象限定符(在左侧)返回工作表特定名称的集合-在本例中为 ws .因为我们要专门查找工作表名称，所以它应该是 ws.Name ，它返回对象限定符的名称.

• 请参见 Worksheet.Names文档.
• 请参见 Worksheet.Name文档.

因此，考虑到它可能类似于:

 对于本工作簿中的每个ws.Worksheets选择案例名称案例"TOC"'没做什么其他情况'在这里做你的动作结束选择下一个ws 

作为旁注，您也可以使用 If ... Else 语句实现相同的目的，例如:

 对于本工作簿中的每个ws.Worksheets如果ws.Name ="TOC"，则'Note by using ="TOC"必须完全匹配，包括字母大小写.'没做什么别的'在这里做你的动作万一下一个ws 

I’m trying to loop a Macro that will run through all worksheets, Except the "TOC" sheet.
-Each worksheet will have a Table Created, formatted, and have formulas put in.

However, the macro tries to begin on the "TOC" sheet, which then causes an error.

I currently have:

Sub BrandRank_()

Dim wb As ThisWorkbook
Dim ws As Worksheet
Dim TableName As String
Dim LstObj As ListObjects
Dim LastRow As Long

For Each ws In ThisWorkbook.Worksheets
Select Case ws.Name
Case Is = "TOC"

Case Else
With ws

‘Insert Table
TableName = "MyTable"
Range("A3").CurrentRegion.Select
ActiveSheet.ListObjects.Add(xlSrcRange, Selection.CurrentRegion, , xlYes).Name = TableName

'Apply a filter to $ Share for all Brands (Largest to Smallest)
ws.AutoFilter.Sort.SortFields.Clear
ws.AutoFilter.Sort.SortFields.Add2 Key:=Range("C3"), SortOn:=xlSortOnValues, Order:=xlDescending
ws.AutoFilter.ApplyFilter
ActiveSheet.ListObjects("Table1").ShowAutoFilterDropDown = False

'More Table Formatting Code

End With
End Select
Next ws
End Sub`

Update below to reflect Samuel Everson's suggestions:

In my scenario, Sheet1 (TOC) is the WorkSheet I want to skip over. So, should I write the code this way..?

Sub LoopThroughWorkSheets_()

    Dim wb As ThisWorkbook
    Dim ws As Worksheet
    Dim TableName As String
    Dim LstObj As ListObjects

    Set ws = ThisWorkbook.Sheets("Sheet2")

For Each ws In ThisWorkbook.Worksheets
    Select Case ws.Names
    Case Is = "TOC"

    Case Else
        With ws

'Insert Table with the Data starting in Column A, Row 3
    TableName = "MyTable"
    .Range("A3").CurrentRegion.Select
    .ListObjects.Add(xlSrcRange, Selection.CurrentRegion, , xlYes).Name = TableName

'Apply a filter to C3 (Largest to Smallest)
    .AutoFilter.Sort.SortFields.Clear
    .AutoFilter.Sort.SortFields.Add2 Key:=Range("C3"), SortOn:=xlSortOnValues, Order:=xlDescending
    .AutoFilter.ApplyFilter
    .ListObjects("TableName").ShowAutoFilterDropDown = False

'Add a Formula to F4, and Copy Down to Last Row in F (Using Column A as the LastRowData Reference)
    .Range("F4").Formula = "=(E4/(E4-G4))-1"
    .Range("F4").NumberFormat = "0.0%"
    .Range("F4").AutoFill Destination:=Range("F4:F" & Range("A" & Rows.Count).End(xlUp).Row)

'Update Number Format in Column E & G
    .Columns("E").NumberFormat = "$#,##0"
    .Columns("G").NumberFormat = "$#,##0"


        End With
    End Select
Next ws

End Sub

解决方案

As pointed out in comments, you're not using your With ws statement. I saw this issue arrise in another one of your questions earlier in the month too so I thought I'd give an explanation for how to use the statement correctly.

Consider this code:

Sub WithStatementExample()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

With ws
    Range("A1").Value = "This cell is A1 and this sheet name is " & ws.Name
    ActiveSheet.Range("A2").Value = "This cell is A2 and this sheet name is " & ws.Name
End With

End Sub 

Now although our statements are encapsulated within the With statement, the output to cells A1 and A2 will actually both be on whatever sheet is active at the time the code is executed.

As example, consider this new workbook with 3 sheets, all with default names:

If we execute the code based on the workbook in that screenshot, the results will go where we are assuming they will - Sheet1 cells A1 and A2. But if we run the code whilst Sheet3 is active, the code outputs the values to Sheet3 cells A1 and A2.

This is because within our With statement, we have not used our ws worksheet as the object qualifier for the Range property.

The range property can apply to both the Application and Worksheet objects and per Application.Range documentation:

When used without an object qualifier, this property is a shortcut for ActiveSheet.Range (it returns a range from the active sheet; if the active sheet isn't a worksheet, the property fails).

To get the statements within the With block using the object, type a . in front of your statements:

Sub WithStatementExample()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

With ws
    .Range("A1").Value = "This cell is A1 and this sheet name is " & ws.Name
    .Range("A2").Value = "This cell is A2 and this sheet name is " & ws.Name
End With

End Sub 

Now the output will only go to the sheet assigned to the ws variable - in this case, Sheet1.

If you need some statements to be applied to other sheets but executed within your With block, you are better off using an explicit reference to the workbook rather than ActiveSheet - This will help explain more - (How to avoid using select in vba)

So if the ListObjects in your code were actually on Sheets("TOC") not whichever ws the loop is referring to, I'd change ActiveSheet.ListObjects... and ActiveSheet.ListObjects("Table1")... to:

wb.Sheets("TOC").ListObjects...
wb.Sheets("TOC").ListObjects("Table1")...

---

With your For Each...Next loop, your variable ws in this case represents an object - specifically a worksheet. The variable ws is assigned the next object at each iteration, so you don't need to set the worksheet object to the variable independently to the loop.

In my example above I had 3 sheets. The two below code blocks will achieve the same outcome, one via a For Each...Next loop and the other without.

For Each...Next approach:

Sub ForEachExample()
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
    Debug.Print ws.Name
Next ws
End Sub

No loop approach:

Sub NoLoopExample()
Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets(1)     'The same as Sheets(1) or Sheets("Sheet1")
Debug.Print ws.Name

Set ws = ThisWorkbook.Sheets(2)         'The same as Worksheets(2) or Sheets("Sheet2")
Debug.Print ws.Name

Set ws = ThisWorkbook.Sheets("Sheet3")  'The same as Worksheets(3) or Sheets(3)
Debug.Print ws.Name

End Sub

Both of them will output the following to the immediate window in the VBE:

Sheet1
Sheet2
Sheet3

In your updated code, you are setting ws to ThisWorkbook.Sheets("Sheet2").

1. You will need to have a worksheet named "Sheet2" in your workbook or you will get a Runtime Error 9: subscript out of range error.
2. The next line in your code is the For Each...Next statement which will set ws to the first sheet in the Worksheets collection, regardless of the previous line of code.

What I'm pointing out is that line of code is redundant in your current procedure and depending on the names of your worksheets, will probably be causing a runtime error.

See the For Each...Next statement documentation.

---

With your Select Case statement, your first line Select Case ws.Names should throw a Compile Error: Type mismatch. ws.Names returns the collection of worksheet specific names based on the object qualifyer (to it's left) - in this case ws. Because we are looking for the sheets name specifically, it should be ws.Name which returns the name of the object qualifyer.

• See the Worksheet.Names documentation.
• See the Worksheet.Name documentation.

So to take that into account it could look something like:

For Each ws In ThisWorkbook.Worksheets
    Select Case ws.Name
        Case "TOC"
            'Do nothing
        Case Else
            'Do your actions here
    End Select
Next ws

As a side note, you could also achieve the same with an If...Else statement like so:

For Each ws In ThisWorkbook.Worksheets
    If ws.Name = "TOC" Then    'Note by using = "TOC" must match exactly, including letter casing. 
        'Do nothing
    Else
        'Do your actions here
    End If
Next ws

这篇关于如何遍历除TOC以外的工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！