creating worksheet tabs from a list

[wnorrick]

I have a worksheet with 85 rows and I would like to create a another worksheet in that workbook for each of the names listed in column A. So I will end up with 86 worksheets in the workbook. is there a way to do this or vba code that will do it? Thank you

 

[shrivallabha]

Hi,


This is plain code without any error handling but it should get you started.


It doesn't check if the sheet exists beforehand. Adjust rows to suit.

[pre]

Public Sub CreateWorksheets()
For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
Sheets.Add(After:=Sheets(Sheets.Count)).Name = Range("A" & i).Value
Next i
End Sub

[/pre]

 

[wnorrick]

I am confused about where to adjust rows. There are 88 rows. Thank you for your help.

 

[shrivallabha]

If you will look at the code posted then I have assumed that first sheet name is written in Cell A1

For i = 1


so if the start cell is A3


then make it


For i = 3

 

[wnorrick]

Thank you

 

[wnorrick]

I am getting an error message when I try to run the macro. It says:


Can't execute code in break mode.


The third line that starts with sheets is the one it highlights in yellow.


any suggestions as to what I am doing wrong?


Thank you again for your help.

 

[Debraj]

Hi wnorrick,


Try This.. by assuming your Index Sheet's name "Sheet1" , change if required

[pre]

Public Sub CreateWorksheets()
With Sheets("Sheet1")
For i = 1 To .Range("A" & .Rows.Count).End(xlUp).Row
Sheets.Add(After:=Sheets(Sheets.Count)).Name = .Range("A" & i).Value
Next i
End With
End Sub

[/pre]

Again without any Error Handler


Regards,

Deb

 

[wnorrick]

Thank you, I will give that a try.

 

[shrivallabha]

Goto Visual Basic Editor and then in:


Debug | Clear All Breakpoints.


or just hit play button (small green triangle for running macros in Visual Basic Editor)

 

[Dhamo]

Try this.


'Assuming you've the sheet names in "Mainsheet".

Public Sub CreateWorksheets()

On Error GoTo ErHndlr

For i = 1 To Range("A" & Rows.Count).End(xlUp).Row

If ws.Name <> Range("A" & i).Value Then

Sheets.Add(After:=Sheets(Sheets.Count)).Name = Range("A" & i).Value

Worksheets("Mainsheet").Select

End If

Next i

Exit Sub

ErHndlr:

Call MsgBox("The worksheet(s) already exists. " &vbctrl & Err.Description, vbCritical)

End Sub