VBA Help Naming Sheets
 

I am lookign for some help on VBA. I need a new sheet named after
each employee that i add to a list. I want simple code that I can
just activate with a button or other command later. So after I add
the employee to a list of names, I want code to be able to go through
the list and find names that are not the same as ones that are already
worksheets and add them. I hope this is possible.

Thanks,
Jay

VBA Help Naming Sheets
 

Sub addsheets()

Set sheetnames = Range("B7:B25")
For Each cell In sheetnames
Found = False
For Each sht In ThisWorkbook.Sheets
If sht.Name = cell Then
Found = True
Exit For
End If
Next sht
If Found = False Then
Worksheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = cell
End If
Next cell

End Sub

"jlclyde" wrote:

I am lookign for some help on VBA. I need a new sheet named after
each employee that i add to a list. I want simple code that I can
just activate with a button or other command later. So after I add
the employee to a list of names, I want code to be able to go through
the list and find names that are not the same as ones that are already
worksheets and add them. I hope this is possible.

Thanks,
Jay

VBA Help Naming Sheets
 

Jay,

Here's one way:

Private Sub CommandButton1_Click()
Dim lRow As Long, i As Range
Dim sh As Worksheet, sh2 As Worksheet
Dim exists As Boolean

lRow = Range("A65536").End(xlUp).Row
Set rng = Range(Cells(1, 1), Cells(lRow, 1))

For Each i In rng
For Each sh In ThisWorkbook.Worksheets
If sh.Name = i Then
exists = True
Exit For
Else
exists = False
End If
Next sh
If exists = False Then
Set sh2 = Sheets.Add
sh2.Name = i
End If
Next i

End Sub

--
Dan

VBA Help Naming Sheets
 

Dan: When you use wroksheet.add you must use either the after:= or befo=.
Otherwise, the new worksheet is put in a new workbook. See my last posting.

"Dan R." wrote:

Jay,

Here's one way:

Private Sub CommandButton1_Click()
Dim lRow As Long, i As Range
Dim sh As Worksheet, sh2 As Worksheet
Dim exists As Boolean

lRow = Range("A65536").End(xlUp).Row
Set rng = Range(Cells(1, 1), Cells(lRow, 1))

For Each i In rng
For Each sh In ThisWorkbook.Worksheets
If sh.Name = i Then
exists = True
Exit For
Else
exists = False
End If
Next sh
If exists = False Then
Set sh2 = Sheets.Add
sh2.Name = i
End If
Next i

End Sub

--
Dan

VBA Help Naming Sheets
 

On Nov 20, 11:33 am, Joel wrote:
Dan: When you use wroksheet.add you must use either the after:= or befo=.
Otherwise, the new worksheet is put in a new workbook. See my last posting.



"Dan R." wrote:
Jay,

Here's one way:

Private Sub CommandButton1_Click()
Dim lRow As Long, i As Range
Dim sh As Worksheet, sh2 As Worksheet
Dim exists As Boolean

lRow = Range("A65536").End(xlUp).Row
Set rng = Range(Cells(1, 1), Cells(lRow, 1))

For Each i In rng
For Each sh In ThisWorkbook.Worksheets
If sh.Name = i Then
exists = True
Exit For
Else
exists = False
End If
Next sh
If exists = False Then
Set sh2 = Sheets.Add
sh2.Name = i
End If
Next i

End Sub

--
Dan- Hide quoted text -

- Show quoted text -

Thanks Joel, but it works fine without.

--
Dan