isssue with excel sheet(urgent)

[ramanapaydala]

Hi,
Could you help me out Regarding this issue Actually i have two excel sheets in one excel sheet i have like scnario1,2,3,4..etc.when i trying to click scenario1 its has to be open sheet2 regarding sheet1 scenario1 detalis like this all scenario what kind coding i have to write please helpout h......

project_projects.xls

THis is issue requirement...

(71.5 KiB) Downloaded 238 times

[anshulilhori]

Hi All,
I have two worksheets i have scenario column in one sheet like scenario1,sceanario2.....in other sheet i have scenario with subnm function dependency what i need to do now when i click on scenario in 2nd sheet it should populate scenario1,then sceanrio 2 and so on please help me with that its really urgent..

Thanks in advance...

[Admin Note: Posts merged into a single thread to prevent people from answering in one thread when further information has been added to the other one.]

[Wim Gielis]

In cell B5 of the second sheet, why don't you choose your scenario over there? And when you're done reading the associated results for that scenario, choose another one?

In any case, the question is not clearly stated, so I'm totally unsure this is what you're after.

Wim

[Alan Kirk]

In cell B5 of the second sheet, why don't you choose your scenario over there? And when you're done reading the associated results for that scenario, choose another one?

In any case, the question is not clearly stated, so I'm totally unsure this is what you're after.

Ah good, it's not just me then.

The VBA code in the workbook seems to involve lots and lots of iteration loops. I'm wondering if the intention is to create a separate reporting sheet for each scenario.

If so, the following quick and dirty code will do it. (BIG-BACKSIDED OL' CAUTION ON THE COPY METHOD! There's a bug in Excel which limits the number of times you can copy a sheet before the method just fails. It's a known bug but not a known number because it depends on the amount of memory that the sheets use. Naturally MS haven't done anything about this because they can't fix bugs and create New! User! Interfaces!

And of course to marketing types the latter are far more important than the former, especially if the icons take up half the screen each (to make them non-threatening and user-friendly, don'cha know) and use lots and lots and lots of vivid primary colours. It's not usually a problem for a workbook of about a dozen sheets though.)

This code obliterates the SubNm formula but if each sheet relates to a scenario I presume that doesn't matter. The code could be tweaked to reinstate a SubNm formula but I ain't going to bother when I don't even know for certain that this is the answer sought.

Code: Select all

Sub MakeScenarioSheets()
'Alan Kirk 13-Nov-2009

Dim rng_Temp As Excel.Range

Dim wks As Excel.Worksheet

On Error GoTo ErrorHandler

Application.Calculation = xlCalculationManual

'Name the range A7 to A18 in sheet 1 as "AllScenarios".
For Each rng_Temp In Sheet1.Range("AllScenarios")

    Sheet2.Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)

    Set wks = ActiveSheet

    With wks
        'Not critical if it can't be renamed.
        On Error Resume Next
        .Name = rng_Temp.Value
        On Error GoTo ErrorHandler
        .Range("B5").FormulaR1C1 = rng_Temp.Value

    End With

Next

Application.Calculate

ExitPoint:

On Error Resume Next

Set rng_Temp = Nothing
Set wks = Nothing

Exit Sub

ErrorHandler:

MsgBox "Error! Number " & Err.Number & vbCrLf & Err.Description

Resume ExitPoint

End Sub