Method 'Union' of object '_Global failed

Question

Update: I realized that I can't use union on multiple sheets. What's the best choice that I have then?

I simply want to combine all sheets in the workbook into the first worksheet.

After I went through the existing questions, I've tried adding Set rng = nothing to clear my range, but it didn't help.

Sub Combine()

     Dim J As Long
     Dim Combine As Range
     Dim rng As Range


      'I want to start from the second sheet and go through all of them
      For J = 2 To Sheets.Count    

      With Sheets(J)
      Set rng = .Range("A1", .Range("A" & .Rows.Count).End(xlUp))
      End With

      For Each Cell In rng
                If Combine Is Nothing Then
                Set Combine = Cell.EntireRow
            Else
                Set Combine = Union(Combine, Cell.EntireRow)
            End If

      Next Cell
      Set rng = Nothing
      Next J

    'Paste the whole union into the 1st sheet
    Combine.Copy Destination:=Sheets(1).Range("A1")

End Sub

All this code gets me an error Method 'Union' of object '_Global failed

Update 2

Sub Combine2()

 Dim rowcount As Long


  For Each Sheet In Sheets

  If Sheet.Index <> 1 Then
  rowcount = Sheet.UsedRange.Rows.Count

  Sheet.UsedRange.Copy Destination:=Sheets(1).Cells(Lastrow + 1, 1)
  Lastrow = Lastrow + rowcount

  End If
  Next Sheet


End Sub

Really simple code, worked perfectly, thanks to @luuklag for leading me on this.

Your approach is ok, but you can't use union on an entire row or column, because it would create a range that is greater than the max. It can be done if you go cell by cell and get the values. — rohrl77
You would need to loop through all sheets, and copy the usedrange to your first sheet. — Luuklag
@rohrl77 I used union on the whole rows before, I think the problem here might be that union doesn't work with multiple sheets. — Juras Sulcas
See for example this: stackoverflow.com/questions/19182360/… — Luuklag

Stavros Jon Stavros Jon · Accepted Answer · 2019-07-03T07:55:11

Indeed .Union method doesn't work across worksheets.

Instead, you could try looping through all your worksheets, copying the corresponding range and pasting it to the destination worksheet.

Something like the following would achieve this:

Sub test()
Dim destinationSheet As Worksheet
Dim sht As Worksheet
Dim destinationRng As Range
Dim rng As Range
Set destinationSheet = ThisWorkbook.Worksheets("Name of your worksheet")

For Each sht In ThisWorkbook.Worksheets

    If sht.Name <> destinationSheet.Name Then

        With sht
            Set rng = .Range(.Range("A1"), .Range("A" & .Rows.Count).End(xlUp))
            rng.Copy
        End With

        With destinationSheet
            Set destinationRng = .Range("A" & .Rows.Count).End(xlUp)
            If destinationRng.Address = .Range("A1").Address Then
                destinationRng.PasteSpecial xlPasteValues
            Else
                destinationRng.Offset(1, 0).PasteSpecial xlPasteValues
            End If
        End With
    End If
Next sht

End Sub

The code above pastes the ranges one by one, in the same column. It can be easily modified to paste the ranges in different columns, one next to the other.

Method 'Union' of object '_Global failed

1 Answers