I am using the below code to copy data on condition from specific sheet to another in same workbook , it works but now I need to loop through some sheets to compact vba code , I use this loop on other macros without problem

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

But in below code I got error Method 'Union' of object '_Global' failed on this line 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

CodePudding user response：

You can not "union" ranges from different sheets.

A range always belongs to a worksheet = parent. The result of UNION is a new range. That's why UNION won't work with ranges from different worksheets.

You are re-using rngU in each loop - so for the first time it is created from ranges from sheet1, then in the next loop you try to add ranges from sheet2 to rngU that contains already ranges from sheet1 - that won't work. What you can do: set rngU = nothing at the end of each cycle