Code to create worksheet names based on the values in the selectedrange

[Mike C]

Is there any chance someone would know some code that allows me to
create new worksheets with the names of a selection of cells.

So, for example, if I selected the following cells:

Rice
Macaroni
Chicken
Hash Browns

I would like the macro to create four sheets by those names.

Any suggestions would be appreciated.

 

[Chip Pearson]

Try some code like the following. The code tests whether a sheet already
exists with the particular name, and won't create a new sheet if that name
is already in use, but it doesn't test whether the name is valid for a new
worksheet.

Sub CreateSheetsFromList()
Dim R As Range
For Each R In Selection.Cells
If R.Text <> vbNullString Then
If SheetExists(R.Text, ThisWorkbook) = False Then
With ThisWorkbook.Worksheets
.Add(after:=.Item(.Count)).Name = R.Text
End With
End If
End If
Next R
End Sub

Private Function SheetExists(SHName As String, WB As Workbook) As Boolean
On Error Resume Next
SheetExists = CBool(Len(WB.Worksheets(SHName).Name))
End Function


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Mark Ivey]

Here is a rough one...

Mark

Sub test()
Dim row As Long
Dim mysheetname As String
Dim myworksheet As String

myworksheet = ActiveSheet.Name

For row = 1 To 4
mysheetname = Cells(row, 1).Text
Sheets.Add
ActiveSheet.Name = mysheetname
Sheets(myworksheet).Select
Next
End Sub