CODEWITHSUNDEEP
vbaexcel
R. Anon
R. Anon

Reputation: 15

VBA Method 'Union' of object '_Global' failed

I'm trying to write a macro that loops through all the Workbooks in a folder, and for each one sends an email with a range of rows that meet criteria. When I run the macro, it does this for the first file but stops at the second giving the error "Method 'Union' of object '_Global' failed", pointing to the line "Set rng2 = Union(rng2, row)". Below is the relevant code:

Sub LoopThroughFiles()

Dim File As String

File = Dir("FilePath\")

While (File <> "")

    Set WorkBk = Workbooks.Open("FilePath\" & File)

    Dim rng As Range
    Dim row As Range
    Dim rng2 As Range
    Dim strbody As String

    Dim OutApp As Object
    Dim OutMail As Object

    Set rng = Range("B52:I200")

    For Each row In rng.Rows
        If row.Columns(7) >= Date Then
            If Not rng2 Is Nothing Then
                'Below is the line that gets the error
                Set rng2 = Union(rng2, row)
            Else
                Set rng2 = row
            End If
        End If
    Next

    'Email code removed

    WorkBk.Close savechanges:=True

    File = Dir()

Wend

End Sub

Any help would be greatly appreciated!

Upvotes: 0

Views: 1605

Answers (1)

Comintern
Comintern

Reputation: 22205

You're attempting to Union with the same range that you built using the previous Workbook. You need to clear the rng2 for each file you process:

WorkBk.Close savechanges:=True
Set rng2 = Nothing  '<---You just closed the workbook this range was built with.
File = Dir()

Upvotes: 4

Related Questions