Creat worksheets and name them from a range of cells

**furiousfox** · 07-07-2009, 04:04 AM

Hi All
I have a range cells (A1:A20) each have a word inside.

How can I create a macro to generate/populate 20 worksheets that the names taken from the cells?

Thanks in advance for your help

Amit

P.S
This one doesn't help http://www.excelforum.com/excel-work...heet-name.html

**DonkeyOte** · 07-07-2009, 06:12 AM

If we assume you're saying Sheet1!A1:A20 contains 20 words, you want to create a sheet for each word naming the sheet per the word ...

Public Sub CreateSheets()
Dim bSheet As Byte, strSName As String
For bSheet = 1 To 20 Step 1
    strSName = Sheets("Sheet1").Cells(bSheet, "A")
    If LenB(strSName) Then
        Sheets.Add After:=Sheets(Sheets.Count)
        On Error GoTo SheetFail
        ActiveSheet.Name = strSName
        On Error GoTo 0
    End If
ResumeHere:
Next bSheet
Exit Sub

SheetFail:
'either invalid naming convention or sheet exists already - delete newly added sheet
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
MsgBox "Failed to Create Sheet : " & strSName, vbCritical, "Error"
Resume ResumeHere

End Sub

**Jbentley** · 07-07-2009, 06:27 AM

Give this a go..

Sub Add_ManySheets()
'
'
'

''' !!!! MAKE SURE YOU HAVE AN EMPTY ROW AT THE END OF YOUR LIST OF SHEET NAMES !!!!

'Sheets("Your Sheet with Names you want for new Sheets").activate
Sheets("ListOfSheetNames").Activate

'First cell in your list of names

Range("A3").Select 'CHANGE THIS TO "A1" IF THATS WHERE YOUR LIST OF SHEET NAMES STARTS

'count the number of rows used in your list
NumberOfNames = ActiveCell.CurrentRegion.Rows.Count

'Create a For..Next Loop using the NumberOfNames
For SheetAddLoop = 1 To NumberOfNames
'Get cell contents to use for new sheetname, using the count of names to offest from the first name in the list
MyNewSheetName = CStr(Sheets("ListofSheetNames").Range("A3").Offset(SheetAddLoop - 1, 0).Value)
    'add a new sheet after the last one
    Sheets.Add After:=Sheets(Sheets.Count)
    'select the new sheet
    ActiveSheet.Select
    'rename the new sheet as the
    ActiveSheet.Name = MyNewSheetName
    ActiveSheet.Range("A1").Select
    
Next
End Sub

Should work fine - I added six new sheets using this.

Beware !!! It's likely to crash if one of the sheet names is already used..

**furiousfox** · 07-07-2009, 07:30 AM

Hi DonkeyOte & Jbentley

Thanks so much for the fast replay!!

I have tested both scripts, and they works great

Thanks for your help

FuriousFox

**JBeaucaire** · 07-07-2009, 07:45 AM

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)