如何让我的 VBA 代码遍历工作簿中的所有工作表?

Question

我目前正在使用以下代码删除表中不需要的分类：

Sub RemoveOldPlatforms()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("RAW")
ws.Range("$A$1:$J$100000").AutoFilter Field:=2, Criteria1:=Array("Coniferous", "Broafleaf", "Mixedwood", "Water", "Exposed Land / Barren", "Urban / Developed", "Greenhouses", "Shrubland", "Wetland", "Grassland"), Operator:=xlFilterValues
ws.Range("$A$2:$J$100000").SpecialCells(xlCellTypeVisible).EntireRow.Delete
ws.Range("$A$1:$J$100000").AutoFilter
End Sub

照原样，我正在指定一个已识别的工作表，但是如何将其遍历工作簿中的所有工作表(20+(？

Answer 1

嗨，非常简单的代码来检查工作簿中的每个工作表，

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
'called once per worsheet
Next ws

在这里，我遍历所有工作表以查找另一个工作簿中可用的所有表，并将其显示在ComboBox中

Private Sub UpdateTablesFromFile()
Dim wb As Workbook
Dim ws As Worksheet
Dim tbl As ListObject
Dim text As String
Dim I As Integer
Dim FileToOpen As String
FolderPath = Application.ActiveWorkbook.Path
FilePath = FolderPath & "" & ComboBox1.Value 
Application.ScreenUpdating = False
Workbooks.Open Filename:=FilePath
For Each ws In Workbooks(ComboBox1.Value).Worksheets
For Each tbl In ws.ListObjects
text = ws.Name & "" & tbl.Name
ImportForm1.ComboBox2.AddItem text 'add every tables in my entire workbook inside the ComboBox2
Next tbl
Next ws
Workbooks(ComboBox1.Value).Close SaveChanges:=False
Application.ScreenUpdating = True
End Sub

Answer 2

你应该做这样的事情：

Sub EnteringAllSheetsOneByOne()
For Each ws In Excel.Workbooks("YourWorkbook.xlsx").Worksheets
ws.Select
Call RemoveOldPlatforms(ws) 'must to be called here and use ws as parameter
Next ws
MsgBox "Done!"
End Sub
' Just add the "ws" parameter to your current sub
Sub RemoveOldPlatforms(ws As Object)
'Dim ws As Worksheet
'Set ws = ThisWorkbook.Worksheets("RAW")
ws.Range("$A$1:$J$100000").AutoFilter Field:=2, Criteria1:=Array("Coniferous", "Broafleaf", "Mixedwood", "Water", "Exposed Land / Barren", "Urban / Developed", "Greenhouses", "Shrubland", "Wetland", "Grassland"), Operator:=xlFilterValues
ws.Range("$A$2:$J$100000").SpecialCells(xlCellTypeVisible).EntireRow.Delete
ws.Range("$A$1:$J$100000").AutoFilter
End Sub

注意：请注意我评论的所有内容，在这种情况下Dim和Set都没有必要