Refer to a sheet in a cell
 

Hi

I am trying to refer to a sheet in a cell.
If the sheet does not exist, the macro will make it. Else it jus
select it.
The first part works, but i cant make it select the new sheet.

My code:

Sub Makro1()

receptnr = Sheets("Main").Range("b1")

If Not SheetExists(Sheets("Main").Range("b1")) Then
Sheets.Add
ActiveSheet.Name = receptnr
Sheets(receptnr).Select ' <-- that line causes the problem
Else
Sheets(receptnr).Select ' <-- and that one.
End If

End Sub

---

I am not very good at programming, so i may have to do this al
different. ;)

Esbe

--
Message posted from http://www.ExcelForum.com

Refer to a sheet in a cell
 

I have revised your code.

'-------------------------------------------
Sub Makro1()
Dim receptnr As Range
On Error GoTo MakeSheet
'- next line produces an error if sheet does not exist
Set receptnr = Sheets("Main").Range("b1")
Application.Goto reference:=receptnr
Exit Sub
'------------
MakeSheet:
Sheets.Add
ActiveSheet.Name = "Main"
Resume
End Sub
'-----------------------------------------

--
Message posted from http://www.ExcelForum.com

Refer to a sheet in a cell
 

hmm, that just selects the cell B2 in Main sheet.

What I want is to make a new sheet with the name written in B2 an
change to that.

If a sheet with the name written in cell B2 already exists, i just wan
to change to that sheet

--
Message posted from http://www.ExcelForum.com

Refer to a sheet in a cell
 

<<hmm, that just selects the cell B2 in Main sheet.
But if sheet "Main" does not exist, it will make it. So what you really
want is :-

'-------------------------------------------
Sub Makro1()
Dim receptnr As Range
Dim SheetName As String
'-------------------------------
SheetName = Sheets("Main").Range("b1").Value
On Error GoTo MakeSheet
'- next line produces an error if sheet does not exist
Set receptnr = Sheets(SheetName).Range("b1")
Application.Goto reference:=receptnr
Exit Sub
'------------
MakeSheet:
Sheets.Add
ActiveSheet.Name = SheetName
Resume
End Sub
'------------------------------------------


---
Message posted from http://www.ExcelForum.com/