VBA Range Merge -- run-time error 1004

[wd314]

I am attempting to merge several worksheets into one range. I have code as follows:

    ' rHistory is defined as a Range above and contains a Range
    Dim rTemp As Range
    For i = 2 To nNumHistory
        sRangeName = "'Sheet" & i & "'!A1:F100"  ' modified example
        Set rTemp = Range(sRangeName)
        Set rHistory = Union(rHistory, rTemp)
    Next i

However, when the Union procedure attempts to execute, I get the following error:
Run-time error '1004':
Method 'Union' of object '_Global' failed

Any help would be greatly appreciated.

 

[VoG]

Hello and welcome to MrExcel.

I may be wrong but

- shouldn't it be Application.Union

- I think that Union can only be used with ranges on the same sheet.

 

[wd314]

Application.Union generates a 1004 error as well...

 

[VoG]

As I said, I don't think that you can use Union with multiple sheets.

You may want to take a look here http://msdn.microsoft.com/en-us/library/cc793964.aspx