Page 1 of 1
cognos TM1 issue...................
Posted: Fri Nov 13, 2009 4:54 am
by 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......
isssue with excel sheet(urgent)
Posted: Fri Nov 13, 2009 5:09 am
by 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.]
Re: isssue with excel sheet(urgent)
Posted: Fri Nov 13, 2009 10:28 am
by 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
Re: isssue with excel sheet(urgent)
Posted: Fri Nov 13, 2009 10:54 am
by Alan Kirk
Wim Gielis wrote: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