Check if sheet exists, create it if it doesn't

[ossuary]

Hello gurus,

Quick question... Can someone please tell me how to use VBA to check whether or not a worksheet named "test" exists in a workbook, and if it does not, create it?

Thanks in advance.

 

[Richard Schollar]

Hi

Perhaps:

  On Error Resume Next
  Set sht = Worksheets("Test")
  If sht Is Nothing Then Worksheets.Add:ActiveSheet.Name = "Test"
  On Error Goto 0

 

[xld]

    On Error Resume Next
        If Not Worksheets("Test").Name = "Test" Then Worksheets.Add.Name = "Test"
    On Error GoTo 0

 

[ossuary]

Thanks for the reply.

I need to do it without any possibility of an error pass. I have code to search whether or not a file exists:

        With Application.FileSearch
            .NewSearch
            .LookIn = ThisWorkbook.Path & "\Core Files\Employee Journals"
            .SearchSubFolders = False
            .Filename = OpenedFile
            .MatchTextExactly = True
            .FileType = msoFileTypeExcelWorkbooks
        If .Execute() = 1 Then

I was hoping to have something similar to check the worksheets, rather than using an error check to bypass the sheet creation. I'm just not sure what the syntax would be. Is there another way to do this?

Also, is there a way to specify what name an added worksheet will have, or do you have to have it be the active worksheet in order to rename it? The problem is that I will have multiple workbooks open when this code is executed, and it is possible that the workbook that is being modified will not be the active workbook, which could cause errors.

 

[ossuary]

    On Error Resume Next
        If Not Worksheets("Test").Name = "Test" Then Worksheets.Add.Name = "Test"
    On Error GoTo 0

xld,

I'll give this a try and see if it works for what I'm trying to do. Thanks.

 

[ossuary]

xld,

That did exactly what I needed. Thanks a bunch!