Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Check if sheet exists, if not create -VBA [duplicate]

Tags:

excel

vba

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
like image 729
Error 1004 Avatar asked Feb 22 '19 09:02

Error 1004


2 Answers

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
like image 168
Siddharth Rout Avatar answered Oct 19 '22 06:10

Siddharth Rout


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
like image 31
Nathan_Sav Avatar answered Oct 19 '22 07:10

Nathan_Sav