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