Opening a new tab

  • Thread starter Thread starter Ed Davis
  • Start date Start date
E

Ed Davis

I would like to open a new tab with the name that is in a cell.
Example in Cell b10 I have a number in this case it is 13245 and would like
to open a new tab with that number 13245.
The number will change many times.
 
How about placing a button from the Forms toolbar on that worksheet. Then you
can change the value in the cell and click the button when you're ready.

Option Explicit
Sub testme()

Dim myCell As Range
Dim wks As Worksheet

Set myCell = ActiveSheet.Range("B10")

Set wks = Nothing
On Error Resume Next
Set wks = Worksheets(CStr(myCell.Value))
On Error GoTo 0

If wks Is Nothing Then
'create a new sheet
Set wks = Worksheets.Add
'try to use that name, may fail if value is invalid
On Error Resume Next
wks.Name = myCell.Value
If Err.Number <> 0 Then
Err.Clear
MsgBox wks.Name & " was created, but not renamed!"
Else
MsgBox "okie, doke!" 'comment this when done testing
End If
End If
End Sub


If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)
 
And I forgot to turn error checking back on:

Option Explicit
Sub testme()

Dim myCell As Range
Dim wks As Worksheet

Set myCell = ActiveSheet.Range("B10")

Set wks = Nothing
On Error Resume Next
Set wks = Worksheets(CStr(myCell.Value))
On Error GoTo 0

If wks Is Nothing Then
'create a new sheet
Set wks = Worksheets.Add
'try to use that name, may fail if value is invalid
On Error Resume Next
wks.Name = myCell.Value
If Err.Number <> 0 Then
Err.Clear
MsgBox wks.Name & " was created, but not renamed!"
Else
MsgBox "okie, doke!" 'comment this when done testing
End If
On Error Goto 0 '<-- added
End If
End Sub
 
Back
Top