我在工作簿中循环浏览选项卡时遇到了问题。我正在处理的代码应该执行以下操作:
- 遍历除标题为"BOAT"和"Data"的工作表之外的所有工作表
- 在循环访问的每个工作表中选择单元格"A2"(A2 包含要过滤的值(,并将其用作"数据"选项卡的自动筛选值
- 然后将过滤后的数据复制并粘贴到循环访问的相应选项卡中。
我遇到的问题是我的代码没有在循环中的活动工作表上拾取。有没有办法为当前正在循环的工作表创建一个变量?
下面的代码。谢谢!
Sub updatedata()
Dim ws As Worksheet
Dim wsheet2 As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.name <> "BOAT" And ws.name <> "Data" Then
Call filter1
End If
Next ws
End Sub
Sub filter1()
Dim lastrow As Long
Dim lastrow2 As Long
Dim wSheet As Worksheet
Dim rInput As String
Application.DisplayAlerts = False
Set wSheet = ActiveSheet
rInput = wSheet.Range("A2").Value
Sheets("Data").Activate
lastrow = Range("A" & Rows.Count).End(xlUp).Row
ActiveSheet.Range("A1:Y" & lastrow).AutoFilter field:=4, Criteria1:="=*" & rInput & "*"
lastrow2 = Range("G" & Rows.Count).End(xlUp).Row
Range("G1:G" & lastrow2).Copy
wSheet.Activate
Range("A4").PasteSpecial xlPasteValues
Rows(4).EntireRow.Delete
Application.DisplayAlerts = True
End Sub
"有没有办法为当前正在循环的工作表创建一个变量?">
是的,在filter1
中使用Worksheet
变量作为参数。 避免在未指定Worksheet
的情况下使用Activate
或进行Range
调用。
Sub updateData()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "BOAT" And ws.Name <> "Data" Then
filter1 ws 'no need to use Call
End If
Next ws
End Sub
通过将ws
作为参数传递给filter1
,所有Range
调用都完全限定了相关Worksheet
。这很容易通过With...End With
块来实现 - 注意.Range("A2").Value
、.Range("A4")
等前面的周期.
- 相当于myWs.Range("A2").Value
、myWs.Range("A4")...
等。
Sub filter1(myWs As Worksheet)
Dim lastRow As Long, lastRow2 As Long
Dim rInput As String
Application.DisplayAlerts = False
With myWs
rInput = .Range("A2").Value
With .Parent.Sheets("Data")
lastRow = .Range("A" & .Rows.Count).End(xlUp).Row
.Range("A1:Y" & lastRow).AutoFilter field:=4, Criteria1:="=*" & rInput & "*"
lastRow2 = .Range("G" & .Rows.Count).End(xlUp).Row
.Range("G1:G" & lastRow2).Copy
End With
.Range("A4").PasteSpecial xlPasteValues
.Rows(4).EntireRow.Delete
End With
Application.DisplayAlerts = True
End Sub