A range has a parent. The sheet on which it is located. since the result
of a union is a range, then you can't union ranges on different sheets.

I see you try to intersect later in your routine. You will get an error if
you try to intersect with ranges on different sheets. You need to check
and see if they are on the same sheet first. But now I am starting to
repeat myself.

--
Regards,
Tom Ogilvy




"sloth" wrote in message
oups.com...
Tom,

I used the union method to create a range with all the values I want to
write to output. Everything worked fine in my test workbook, but when
I brought it into my real workbook everything blew up.

I keep getting a union error.

Is there a problem with trying to create a range based on union of
ranges from different worksheets? My ranges are on multiple sheets.
If so, is there a work around that you know of?



Here's my code (Note that ranges in the Union are on different sheets):


Sub NameOutput(MaxScenId As Integer, Optional OutputWks As String)

Dim rng1 As Range, rng2 As Range
Dim cell As Range
Dim rngScenId As Range
Dim rngScenData As Range
Dim nm As Name
Dim sOutputWks As String, sDataAddress As String, sDataCatAddress As
String

Dim i As Integer, r As Integer, c As Integer, p As Integer

Set rng1 = Union(Range(Application.Names!Revenue),
Range(Application.Names!COGS), _
Range(Application.Names!EBIT))

sOutputWks = OutputWks

i = 0
c = 1
r = 1

Set rngScenId = Range(Application.Names("Scenario.Input"))

Do While i <= MaxScenId
rngScenId.Value = i
For Each nm In ThisWorkbook.Names
Set rng2 = Nothing
On Error Resume Next
Set rng2 = nm.RefersToRange
On Error GoTo 0
If LCase(nm.Name) = "scendata" Then
nm.Delete
ElseIf LCase(nm.Name) = "scencatdata" Then
nm.Delete
Else
If Not rng2 Is Nothing Then
If Not Intersect(rng1, rng2) Is Nothing Then
If c = 1 Then
p = 0
'write row headings
For Each cell In rng2
p = p + 1
r = r + 1
Worksheets("Sen2").Cells(r, 1).Value = nm.Name
& p
Next
Else
'write values
For Each cell In rng2
r = r + 1
Worksheets("Sen2").Cells(r, c).Value =
cell.Value
Next
End If
End If
End If
End If
Next

'write column headings
Worksheets("Sen2").Cells(1, c).Value = i

'increment scenario id and column, reset row
c = c + 1
r = 1
i = i + 1

Loop

Worksheets("Sen2").Range("A1").Activate

sDataCatAddress = rngScenData.Rows(1).Address
ThisWorkbook.Names.Add Name:="ScenData", RefersTo:="=Sen2!" &
sDataAddress

Set rngScenData = Range(Application.Names!ScenData)
sDataAddress = ActiveCell.CurrentRegion.Address
ThisWorkbook.Names.Add Name:="ScenCatData", RefersTo:="=Sen2!" &
sDataCatAddress


End Sub