J
JG Scott
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
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