Creating Sheets that Calculate

[Pwnzrvanhax]

Hello guys,

I'm new to the forum and not particularily well versed in VBA so please bear with me. I'm making a scenario analysis of costs for different investments. I'm comparing the as-is (current) situation with several scenarios. I start with a sheet called "as-is" and then create scenarios to compare using a macro. Currently the way I have it set up is that a text box pops up and asks how many scenarios to analyze. Lets say 3 for this example, it will then make 3 "Scenario" sheets (numbered Scenario-1, Scenario-2 and Scenario-3) and 3 "cost comparison" sheets (numbered Scenario-1, Scenario-2 and Scnario-3). The "Scenario" sheet would be where the user inputs all the data for each investment option available. The "Cost Comparison" sheet should only calculate the difference between the "Scenario" sheets and the "as-is" sheet. All sheets have the exact same layout and formating.

I have managed to get the code to create the sheets thus far but have not figured out how to alter the formulas in the "cost Comparison" sheet to calculate the difference yet. The incomplete code is at the end of this post. At then end I need the code to do the following:

1) Create the "Scenario" sheets and "Cost Comparison" sheets (as described above)
2) Change all the formulas in the "Cost Comparison" sheets I just created to compare each newly created "scenario" sheet to the "as-is" sheet. (basically "Scenario" minus "as-is" to calculate delta). To elaborate this means, "Cost Comparison-1" will be "Scenario-1" minus "as-is"; "Cost Comparison -2" will be "Scenario-2" minus "as-is" and so forth.

3) maintain the formatting of the other sheets

Thanks in advance!


<code>

[/FONT]
[FONT=Verdana]Sub CreateScenariosandComparison()[/FONT]
[FONT=Verdana]'Create multiple scenario worksheets for analysis based on how many scenarios you wish to compare[/FONT]
[FONT=Verdana]    Dim I As Long[/FONT]
[FONT=Verdana]    Dim xNumber As Integer[/FONT]
[FONT=Verdana]    Dim xName As String[/FONT]
[FONT=Verdana]    Dim xActiveSheet As Worksheet[/FONT]
[FONT=Verdana]    On Error Resume Next[/FONT]
[FONT=Verdana]    Application.ScreenUpdating = False[/FONT]
[FONT=Verdana]    Set xActiveSheet = ActiveSheet[/FONT]
[FONT=Verdana]    xNumber = InputBox("Enter number of scenarios you wish to compare the As-is situation to")[/FONT]
[FONT=Verdana]    For I = 1 To xNumber[/FONT]
[FONT=Verdana]        xName = ActiveSheet.Name[/FONT]
[FONT=Verdana]        xActiveSheet.Copy After:=ActiveWorkbook.Sheets(xName)[/FONT]
[FONT=Verdana]        ActiveSheet.Name = "Cost Comparison-" & I[/FONT]
[FONT=Verdana]    Next[/FONT]
[FONT=Verdana]    xActiveSheet.Activate[/FONT]
[FONT=Verdana]    Application.ScreenUpdating = True    [/FONT]
[FONT=Verdana]    On Error Resume Next[/FONT]
[FONT=Verdana]    Application.ScreenUpdating = False[/FONT]
[FONT=Verdana]    Set xActiveSheet = ActiveSheet[/FONT]
[FONT=Verdana]    For I = 1 To xNumber[/FONT]
[FONT=Verdana]        xName = ActiveSheet.Name[/FONT]
[FONT=Verdana]        xActiveSheet.Copy After:=ActiveWorkbook.Sheets(xName)[/FONT]
[FONT=Verdana]        ActiveSheet.Name = "Scenario-" & I[/FONT]
[FONT=Verdana]    Next[/FONT]
[FONT=Verdana]    xActiveSheet.Activate[/FONT]
[FONT=Verdana]    Application.ScreenUpdating = True[/FONT]
[FONT=Verdana]End Sub[/FONT]
[FONT=Verdana]

</code>