naming a new scenario with a macro

Posted by Elizabeth on July 27, 2000 1:41 PM

How can I set up a macro to add a new scenario with the name that exists in a cell?

example:
the cell's Name = scenario1
the cell's Value = forecast 7/20

How can I set up my macro so that it sets up a new scenario named after the Value in the cell named scenario1?

Posted by Ryan on July 27, 0100 1:48 PM

Elizabeth,

This will take the value in the named range "scenario1" and set up a new scenario. You will have to change what cell it is changing and the values that go into the changing cell. Hope this helps!

Ryan
ActiveSheet.Scenarios.Add Name:=Range("scenario1").Value, ChangingCells:=Range("A1"), _
Values:=Array("1234"), Locked:=True, Hidden:=False

Posted by elizabeth on July 27, 0100 2:26 PM

Thanks Ryan,
That worked great for getting the name in.

The way that I have this set up, I bring the information in from another sheet into the cell that affect the scenario. This way, the cells are already at their new values before I add the scenario. How do I get my macro to accept the existing cell values as the array values?

Thanks!!!

Posted by Ryan on July 27, 0100 3:28 PM

Elizabeth,

I don't get it! ;-) I would have to see the macro to see what is going on. If you want to post it on here or email it to me I would be able to see what's happening.

Ryan
ufexcel@hotmail.com

Posted by Elizabeth on July 27, 0100 5:37 PM

Ryan,

Thanks so much for your help!

Here is what I have so far. Hope it helps.

copy info from spreadsheet1 to spreadsheet2 into the range affected in the scenario

Sheets("spreadsheet1).Select
Range("scenarioinfo1").Select
Selection.Copy
Sheets("spreadsheet2").Select
Range("scenarioinfo1").Select
ActiveSheet.Paste

copy title of info (which is also the name of the new scenario) from spreadsheet1 to spreadsheet2 into a cell affected in the scenario

Sheets("spreadsheet1).Select
Range("newscenarioname").Select
Selection.Copy
Sheets("spreadsheet2").Select
Range("scenarioname").Select
ActiveSheet.Scenarios.Add Name:=Range("scenarioname").Value,
ChangingCells:=Range("A1:G10"),_Values:=Array("??????"), Locked:=True,
Hidden:=False

note Range(A1:G10) is the same as scenarioinfo1

ActiveSheet.Scenarios.Show (?????)

Here are my two issues:

1. How do I get the macro to recognize that the values for the array are the ones that are currently in the cells (scenarioinfo1)?
2. How do I get the macro to recognize that the scenario it is to show is the one currently in the cell (scenarioname)?