VBA check if sheet exists if not create

Asked 3 years, 7 months ago

Viewed 28k times

7

New! Save questions or answers and organize your favorite content.
Learn more.

I have test many codes which check if a sheet exists (based on name) and if not create one. Some of them loop all sheets and some refer to sheet and if create an error means that sheet does not exist. Which is the most appropriate - orthodox - faster way achieve this task?

Currently I'm using:

Option Explicit

Sub test()     
    Dim ws As Worksheet
    Dim SheetName As String
    Dim SheetExists As Boolean
    
    SheetName = "Test"
    SheetExists = False
    
    With ThisWorkbook
        'Check if the Sheet exists
        For Each ws In .Worksheets
            If ws.Name = SheetName Then
                SheetExists = True
                Exit For
            End If   
        Next
        
        If SheetExists = False Then
            'If the sheet dont exists, create
            .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = SheetName
        End If
    End With 
End Sub

asked Feb 22, 2019 at 9:04

Error 1004Error 1004

7,7873 gold badges20 silver badges40 bronze badges

2

This is what I use. No need to loop. Directly try to assign to an object. If successful then it means that sheet exists :)

Function DoesSheetExists(sh As String) As Boolean
    Dim ws As Worksheet

    On Error Resume Next
    Set ws = ThisWorkbook.Sheets(sh)
    On Error GoTo 0

    If Not ws Is Nothing Then DoesSheetExists = True
End Function

USAGE

Sub Sample()
    Dim s As String: s = "Sheet1"

    If DoesSheetExists(s) Then
        '
        '~~> Do what you want
        '
    Else
        MsgBox "Sheet " & s & " does not exist"
    End If
End Sub

answered Feb 22, 2019 at 9:11

Siddharth RoutSiddharth Rout

144k17 gold badges200 silver badges246 bronze badges

1

Sub solution1()    
    If Not sheet_exists("sheetnotfound") Then
        ThisWorkbook.Sheets.Add( _
                    After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)).Name = _
                    "sheetnotfound"
    End If    
End Sub


Function sheet_exists(strSheetName As String) As Boolean        
    Dim w As Excel.Worksheet
    On Error GoTo eHandle
    Set w = ThisWorkbook.Worksheets(strSheetName)
    sheet_exists = True

    Exit Function 
eHandle:
    sheet_exists = False
End Function

Pᴇʜ

55.1k9 gold badges46 silver badges68 bronze badges

answered Feb 22, 2019 at 9:15

Nathan_SavNathan_Sav

8,3902 gold badges13 silver badges20 bronze badges

VBA Code to Check if Sheet with Name exists?

This Excel vba code will check if sheet with a specific name exists in the workbook.

As per Microsoft specifications, You can add as many number of sheets depending on the limits of Your computer’s memory.

So, this loop will check if any of those sheets has the name passed as parameter to this function.

• There are 2 methods presented in this page.
• Use option 1 first & Test the performance.
• If the loop is taking more time, then use 2nd option.

VBA function to Check if Sheet Exists

Here is the vba code the loop thru all the sheets & verifies the name passed as parameter.

Public Function SheetExists(SheetName As String) As Boolean
    'Declare variables - Officetricks.com
    Dim wSh As Worksheet
    Dim bReturnValue As Boolean
    
    'Loop Thru Each Sheet in workbook
    bReturnValue = False
    For Each wSh In ThisWorkbook.Sheets
    
        'Check whether there is a name match
        If VBA.UCase(wSh.Name) = VBA.UCase(SheetName) Then
            bReturnValue = True
            Exit For
        End If
    Next wSh
    
    'Return Match Result
    SheetExists = bReturnValue
End Function

The above function will return ‘True’ if the sheet with exact name exists in the workbook. Otherwise it will return false.

Lets see another version that is more faster than the above one.

VBA Worksheet Exists with specific Name in Workbook

This function does not loop thru all existing sheet. It directly checks for the Sheet name.

If you face any issue in using this function, then use the first one. Option 1 gives good results consistently.

Public Function fSheetExists(SheetName As String) As Boolean
    'Declare variables - Officetricks.com
    Dim wSh As Worksheet
    Dim bReturnValue As Boolean
    Set wSh = Nothing
    
    'Assign Sheet to Object
    On Error Resume Next
    Set wSh = ThisWorkbook.Sheets(SheetName)
    On Error GoTo 0
    
    'Check if Sheet with name exists
    If wSh Is Nothing Then
        bReturnValue = False
    Else
        bReturnValue = True
    End If
    
    'Return Match Result
    fSheetExists = bReturnValue
End Function

Using this function You can confirm is a sheet exists of not.

Then by using the function in here, you can create or add new worksheet to workbook & rename it. Click here to get the code to add new sheet & rename it.

How do you check if a sheet exist in VBA?

How do you check if a sheet exists in Excel?

Does != Work in VBA?

How do I find a sheet in Excel VBA?