什么是有效的方式来循环通过一些表紧凑的vba代码?

Question

我使用下面的代码将copy data on condition从特定的工作表到同一工作簿中的另一个，它工作，但现在我需要loop through some sheets to compact vba code，我在其他宏上使用这个循环没有问题

Dim ws As Worksheet
For Each ws In Sheets(Array("sheetName", "sheetName", "sheetName"))

但是在下面的代码中，我得到了错误对象'_Global'的方法'Union'失败Set rngU = Union(rngU, ws.Range(ws.Cells(i + 2, 1), ws.Cells(i + 2, lastCol)))

Sub FD_Copy_Data_On_Condition()

Dim ws As Worksheet, sh1 As Worksheet, LastRow As Long
Dim arr_column, rngU As Range, i As Long, lastCol As Long

'Set ws = Sheet05
Set sh1 = Sheet11

For Each ws In Sheets(Array("North", "Central", "South")) ' Loop Through Sheets

LastRow = ws.Cells(Rows.count, 1).End(xlUp).Row    'last row in A:A column
lastCol = ws.UsedRange.Columns.count               'last column, to avoid copying the whole row

arr_column = ws.Range("U3:U" & LastRow).Value2     'put in an array the columns to be processed against "Yes" string
'process both columns in the same iteration to make code faster
For i = 1 To UBound(arr_column)                     'iterate between the array rows and process the columns values

If arr_column(i, 1) = "Yes" Then                'Finding a match in column U:U:
If rngU Is Nothing Then                      'if the rngU keeping the range to be copied is not Set (yet)
Set rngU = ws.Range(ws.Cells(i + 2, 1), ws.Cells(i + 2, lastCol)) 'the range is Set by the used range suitable row
Else
Set rngU = Union(rngU, ws.Range(ws.Cells(i + 2, 1), ws.Cells(i + 2, lastCol))) 'add the suitable row to the existing range
End If
End If

Next i

'------ Paste Data
If Not rngU Is Nothing Then 'if rngU has been set (it contains at least a row), copy it in Sheet1
rngU.Copy Destination:=sh1.Range("A" & sh1.Rows.count).End(xlUp).Offset(1) 'copy the range at once
End If
'--------------------
Next ws
End Sub

Answer 1

您不能"联合";范围从不同的表

区域始终属于工作表= parent。UNION的结果是一个新的范围。这就是为什么UNION不能处理来自不同工作表的范围。

您在每个循环中重用rngU -因此，第一次它是从sheet1的范围创建的，然后在下一个循环中，您尝试添加从sheet2到rngU的范围，其中包含已经从sheet1的范围-这将不起作用。您可以做的是:在每个循环结束时set rngU = nothing