Creating Multiple Sheets if they do not Exist

[AgaG8]

Hi,

I am relatively new to VBA and so far I love it.

I am trying to write a macro that will create sheets and fill data into them. I would like for my macro to create the sheets if they do not exist or, if they do exist, then move onto the next steps.

So far I have this code..

    For i = 1 To Worksheets.Count
    If Worksheets(i).Name = "AssemblyType" Then
        exists = True
    End If
    Next i
    If Not exists Then
        Sheets.Add(After:=Sheets(Sheets.Count)).Name = "AssemblyType"
    End If
    For i = 1 To Worksheets.Count
    If Worksheets(i).Name = "ParentChild" Then
        exists = True
    End If
    Next i
    If Not exists Then
        Sheets.Add(After:=Sheets(Sheets.Count)).Name = "ParentChild"
    End If
    For i = 1 To Worksheets.Count
    If Worksheets(i).Name = "OutputData" Then
        exists = True
    End If
    Next i
    If Not exists Then
        Sheets.Add(After:=Sheets(Sheets.Count)).Name = "OutputData"
    End If

This code works if the first "AssemblyType" sheet does not exist, if it does and the next sheets do not exist, it does not create them.

I am sure there is an obvious flaw, as I am very new.

Also, I would prefer to not use Dim yet, I plan to learn that soon, once I get a better understanding of the basics.

Thanks.

 

[mumps]

"Dim" is an integral part of coding. Try This macro:

Sub CreateSheets()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Dim sheetArray As Variant
    sheetArray = Array("AssemblyType", "ParentChild", "OutputData")
    Dim i As Long
    For i = LBound(sheetArray) To UBound(sheetArray)
        Set ws = Nothing
        On Error Resume Next
        Set ws = Worksheets(sheetArray(i))
        On Error GoTo 0
        If ws Is Nothing Then
                Worksheets.Add(After:=Sheets(Sheets.Count)).Name = sheetArray(i)
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[derHoepp]

Hi there too,

as mumps pointed out, Dim is an important part of programming. Especially during your learning-time, because you force yourself to thinking about your data. In addition to mumps, I'd like to suggest a Function for this purpose, as you'll be checking the same thing multiple times.
To check if a Worksheet exists there are two different ways. Either by looping through all the sheets and checking for the name, or by provoking an error by assigning a Worksheet-Object to a variable. I just wrote both for you. It's up to you to decide, which one you prefer:

Option Explicit

Sub mainProgram()
    If Not SheetExists1("AssemblyType") Then
        Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = "AssemblyType"
    End If
    
    If Not SheetExists2("ParentChild") Then
        Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = "ParentChild"
    End If
End Sub

Private Function SheetExists1(SheetName As String) As Boolean
    'Option 1 - Looping through all sheets
    Dim ws As Worksheet 'Defines a variable that can store Worksheet-Objects
    SheetExists1 = False 'Default return-Value is false
    
    For Each ws In Worksheets 'Loop through all Worksheets in the giveb Workbook
        If ws.Name = SheetName Then
            SheetExists1 = True 'Set the return-Value to true, as a worksheet with the name was found
            Exit For 'Exit loop, as the sheet has already been found
        End If
    Next ws
    
End Function

Private Function SheetExists2(SheetName As String) As Boolean
    'Option 2 - Provoking an error
    Dim ws As Worksheet 'Defines a variable that can store Worksheet-objects
    
    'This method provokes an error, if there is no worksheet with that name.
    'therefore the error handling has to be ignored for exactly one line of code
    On Error Resume Next 'If an error occurs, it will be ignored
        'tries to set a reference to a worksheet by its name from the Worksheets-Collection
        Set ws = Worksheets(SheetName)
    On Error GoTo 0 'If an error occurs, it will not be ignored anylonger
    
    'If there has been an error referencing the sheet, the Variable ws will still be empty.
    'Object-variables have the special state "Nothing" for empty references, therefore we can
    'check for this state to determine if a sheet exists. If it is nothing, the sheet does not exist.
    ' Negating this will answer the question
    
    SheetExists2 = Not ws Is Nothing 'set the return value to true, if ws is not nothing, to false, if ws is nothing
    
End Function

Of course you could also encapsulate the Main program by looping through an array and calling the program.

But enough for now! Have fun with the code!
derHoepp

 

[AgaG8]

Thanks mumps, works like a charm. I'll start learning "Dim".

 

[AgaG8]

Thanks for the comments derHoepp. It helps to see different ways of doing this.

 

[mumps]

You are very welcome.