Creating Sheets from List IF sheet doesn't already exist

Felix1980

New Member
Joined
May 16, 2018
Messages
37
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 :

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.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,951
Try:

VBA Code:
Sub SheetCreate()

Dim newSheetName As String

    On Error Resume Next
    For Each cell In ActiveWorkbook.Sheets("Info").Range("B2:B6")
        newSheetName = cell.Value
        WorksheetExists = False
        For Each Sheet In Worksheets
            If Sheet.Name = newSheetName Then
            WorksheetExists = True
        GoTo nextcell
        End If
    Next Sheet
        Worksheets.Add.Name = newSheetName
nextcell:
    Next

End Sub
 

Felix1980

New Member
Joined
May 16, 2018
Messages
37
This appears to be creating worksheets but not naming them from the cells, just "Sheet3", "Sheet4", etc.
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,951
Working here... What happens when you step through the code (F8) and hover the mouse over newSheetName after it is assigned a value?
 

Felix1980

New Member
Joined
May 16, 2018
Messages
37
I figured out the problem. A previous code, was not correctly switching the column to text from date, just the first line. I corrected that and it now your code works! Thank you!
 

Forum statistics

Threads
1,143,620
Messages
5,719,789
Members
422,244
Latest member
AYSHANA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top