Create worksheet from cell content

Posted by Phil Ridley on July 10, 2001 1:17 AM

Hi all,
I think this one was already covered on this board but I cannot seem to find it. What I want is to create a new worksheet named from the contets of a cell on another worksheet in the same workbook. If someone could point me to the relevant VBA commmand, it would be much appreciated.

Phil.

Posted by JAF on July 10, 2001 2:44 AM

Hiya

The basic code you need is as follows:

Sub Add_Worksheet_Name_From_Cell()
Dim NumberSheets As Integer
NumberSheets = ActiveWorkbook.Worksheets.Count
Sheets.Add After:=Sheets(NumberSheets)
ActiveSheet.Name = Sheet1.[B2].Value
Sheet1.Activate
End Sub

The above will add a new sheet to the end of the existing sheets and name it based on the value in cell B1 on Sheet1

You might need to build in some checks to ensure that the "naming cell is not blank, contains no more than 31 characters and does not contain any illegal characters for sheet names (these are : \ / ? * [ and ] )

Posted by Ivan F Moala on July 10, 2001 2:53 AM

Phil, something like this may help....
change as required.....

Sub AddSheet_NameFromCell()
Dim NewSheetName As String
Dim lastSh As String

lastSh = ActiveSheet.Name
NewSheetName = Sheets("YourSheet").Range("YourcellRange")

ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count)

On Error Resume Next
ActiveSheet.Name = NewSheetName
If Err.Number = 1004 Then
Application.DisplayAlerts = False
ActiveSheet.Delete
MsgBox Err.Number & " : " & Err.Description, _
vbMsgBoxHelpButton + vbOKOnly, "Sheet ErrorAdd", _
Err.HelpFile, Err.HelpContext
Err.Clear
Sheets(lastSh).Activate
Application.DisplayAlerts = True
End If

End Sub

Ivan

Posted by Ivan F Moala on July 10, 2001 2:54 AM

Didn't see your answer JAF ;-)