Excel macro - Opening a sheet in accordance with the value in a particular cell

A

arunjoshi

this is what i want to do.

the first sheet is named "SheetZero", in it the cell A1 contains
value which can vary between 1 and 20. SheetZero also contains a butto
with a macro assigned to it.

additionally there are twenty more sheets named Sheet1, Sheet2, ..
etc., to Sheet20.

when the value in cell A1 in SheetZero is changed, and the macro i
activated by clicking the button, i want another sheet to be opened, i
such a manner that it is based on the value in cell A1.

if the value is 1, then Sheet1 should open. if the value is 2, the
Sheet2 should open. if the value is 3, then Sheet3 should open... so o
and so forth ... if the value is 20, then Sheet20 should open.

please help me with the VB code for the macro. i am a novice in macr
writing, so please help me accordingly.

thanks
 
F

Frank Kabel

Hi
try something like
Worksheets("Sheet" & activesheet.range
("A1").value).activate
 
D

DSC

Hi

you could add this bit of code to the buttons click event

either one or the other

Code:
--------------------

Dim mySheet As Integer
'get value from Cell A1
mySheet = Range("A1").Value
'activates the sheet by position
Worksheets(mySheet + 1).Activate

'or you could do it this way
Dim MySheetName As String
'get name of sheet
MySheetName = "sheet" & Range("A1").Value
'open the sheet by name
Worksheets(MySheetName).Activate

--------------------



HTH

David
 
Top