Hello, I am trying to create a code to go through a list (could be hundreds of cells) and add a new sheet when the cell is not already the name of a sheet. I started with the following code which works :
and tweak it to go through a column instead of just one cell. What I have doesn't seem to move on from the first cell and errors out, though I cannot figure out why.
If someone could show me what I'm doing incorrectly it would be greatly appreciated. The end result would be calling this from the "Final" workbook to pull the sheet names from a "download" workbook and create the sheets in the "Final" workbook.
VBA Code:
Sub TestSheetCreate()
'Creates worksheets
Dim newSheetName As String
Dim checkSheetName As String
newSheetName = Sheets("Info").Range("A2")
On Error Resume Next
checkSheetName = Worksheets(newSheetName).Name
If checkSheetName = "" Then
'create new worksheet
Worksheets.Add.Name = newSheetName
Else
'worksheet exists so do nothing
End If
End Sub
and tweak it to go through a column instead of just one cell. What I have doesn't seem to move on from the first cell and errors out, though I cannot figure out why.
VBA Code:
Sub TestSheetCreate2()
'Creates worksheets
Dim newSheetName As String
Dim checkSheetName As String
Dim xRg As Range
For Each xRg In ActiveWorkbook.Sheets("Info").Range("B2:B6")
newSheetName = xRg
checkSheetName = Worksheets(newSheetName).Name
If checkSheetName = "" Then
'create new worksheet
Worksheets.Add.Name = newSheetName
Else
'worksheet exists so do nothing
End If
Next
End Sub
If someone could show me what I'm doing incorrectly it would be greatly appreciated. The end result would be calling this from the "Final" workbook to pull the sheet names from a "download" workbook and create the sheets in the "Final" workbook.