I am trying to name a range based on a user's selection from a list
box so the procedure can then copy that range to another range. I
don't want the user to see the program moving between sheets, so I am
avoiding using "select". Below is my code so far. I am getting an
"object required" error on the "Set Scenario..." line. Can someone
please explain what I'm doing wrong here? Thanks.

JG Scott




Sub ActivateScenario()

Dim RngToCopy As Range
Dim LastRow As Long
Dim LastCol As Long
Dim ScenarioToShow As Range
Dim Scenarios As Worksheet
Dim Names As ListBox


If Range("CountList").Value = 0 Then

MsgBox ("There are no saved scenarios. To create one, click on
the ""Save Scenario"" button.")

Exit Sub

End If


NameScenarioList

frmScenarios.Show

ActiveWorkbook.Names.Add Name:="ActiveScenario", RefersToR1C1:= _
"=Scenarios!R2C1"


Set ScenarioToShow = Range("ActiveScenario").Value


With Sheets("Scenarios")

LastRow = .Range("ScenarioToShow").Offset(99, 0)
LastCol = .Range("ScenarioToShow").Offset(0, 2)
Set RngToCopy = .Range("ScenarioToShow", .Cells(LastRow,
LastCol))

End With



End Sub

Set ScenarioToShow = Range("ActiveScenario").Value

should be

Set ScenarioToShow = Range("ActiveScenario")

with no value on the end.

--
Regards,
Tom Ogilvy


"JG Scott" wrote:

I am trying to name a range based on a user's selection from a list
box so the procedure can then copy that range to another range. I
don't want the user to see the program moving between sheets, so I am
avoiding using "select". Below is my code so far. I am getting an
"object required" error on the "Set Scenario..." line. Can someone
please explain what I'm doing wrong here? Thanks.

JG Scott




Sub ActivateScenario()

Dim RngToCopy As Range
Dim LastRow As Long
Dim LastCol As Long
Dim ScenarioToShow As Range
Dim Scenarios As Worksheet
Dim Names As ListBox


If Range("CountList").Value = 0 Then

MsgBox ("There are no saved scenarios. To create one, click on
the ""Save Scenario"" button.")

Exit Sub

End If


NameScenarioList

frmScenarios.Show

ActiveWorkbook.Names.Add Name:="ActiveScenario", RefersToR1C1:= _
"=Scenarios!R2C1"


Set ScenarioToShow = Range("ActiveScenario").Value


With Sheets("Scenarios")

LastRow = .Range("ScenarioToShow").Offset(99, 0)
LastCol = .Range("ScenarioToShow").Offset(0, 2)
Set RngToCopy = .Range("ScenarioToShow", .Cells(LastRow,
LastCol))

End With



End Sub