kmoyer
06-02-2014, 07:10 AM
My first post here. I am learning VBA and understand now why it is like learning a new language! I know there is a simple solution to what I want to do, but I am just learning and am stuck.
At my job, I have a current workbook that contains a worksheet titled "Names" which is a master list of all employees and then a worksheet for some of the employees in the list. Some of the employees don't have a worksheet, but I need to create one if that is the case. I would like to create a sub that looks at the list of names in the "Names" worksheet, skips the name if a worksheet exists for it and continues to check the list, and also creates a new worksheet if one doesn't exist for one of the names.
Sub createnamedworksheet ()
Dim ws As Worksheet
Dim NAMES As Range
Dim AllNames As Range
Dim WSName As String
Set AllNames = Sheets("AllNames").Range("NAMES", Range("NAMES").End(xlDown))
For Each NAMES In AllNames
wsName = NAMES.Value
If wsName <> "wsAllNames" Then
Sheets.Add
With ActiveSheet
.Move after:=Worksheets(Worksheets.Count)
.Name = wsName
End With
End If
Next NAMES
End Sub
This sub adds worksheets for the first few names, but as soon as it hits a name for which a worksheet already exists, it errors and tells me it can't add a worksheet
with a name that already exists.
The list of employee names on the Names worksheet can change as employees come and go. If a name is deleted from the list, I will need to figure out a sub to look at the worksheet tabs and delete any worksheets who don't have a corresponding name in the employees worksheet. I haven't even tried going there yet. It would save so much time to be able to automate this task, but I'm wasting so much time trying to figure it out. My boss is getting testy.
I looked around the forum but couldn't seem to find any posts that fit this situation, although some were close. Any advice would be appreciated.
At my job, I have a current workbook that contains a worksheet titled "Names" which is a master list of all employees and then a worksheet for some of the employees in the list. Some of the employees don't have a worksheet, but I need to create one if that is the case. I would like to create a sub that looks at the list of names in the "Names" worksheet, skips the name if a worksheet exists for it and continues to check the list, and also creates a new worksheet if one doesn't exist for one of the names.
Sub createnamedworksheet ()
Dim ws As Worksheet
Dim NAMES As Range
Dim AllNames As Range
Dim WSName As String
Set AllNames = Sheets("AllNames").Range("NAMES", Range("NAMES").End(xlDown))
For Each NAMES In AllNames
wsName = NAMES.Value
If wsName <> "wsAllNames" Then
Sheets.Add
With ActiveSheet
.Move after:=Worksheets(Worksheets.Count)
.Name = wsName
End With
End If
Next NAMES
End Sub
This sub adds worksheets for the first few names, but as soon as it hits a name for which a worksheet already exists, it errors and tells me it can't add a worksheet
with a name that already exists.
The list of employee names on the Names worksheet can change as employees come and go. If a name is deleted from the list, I will need to figure out a sub to look at the worksheet tabs and delete any worksheets who don't have a corresponding name in the employees worksheet. I haven't even tried going there yet. It would save so much time to be able to automate this task, but I'm wasting so much time trying to figure it out. My boss is getting testy.
I looked around the forum but couldn't seem to find any posts that fit this situation, although some were close. Any advice would be appreciated.