VBA Code to Check if Sheet Exists

[OilEconomist]

Thanks in advance, I will try to give feedback for any potential solutions given.

This code was working, but I now get error "Compile error: Method or Member Data not Found"

Sub CheckSht()

 Dim FirstDate As String
 Dim Sheet As Sheets
 
 
    '_____________________________________________________________________________________________
    'Make a copy of "Sheet1" sheet.
        'Get the date of the first one
                Sheets("Sheet1").Activate
                FirstDate = Format(Range("E2").Value, "YYYY.MM.DD")
        
        'Check to see if the sheet exists and if so, terminate the program
            'If ActiveSheet.Name = "Sheet1" & "." & FirstDate
                For Each Sheet In ActiveWorkbook.Worksheets
                    
                    If Sheet.Name = "Sheet1" & "." & FirstDate Then
                        MsgBox "The Worksheet " & """Sheet1." & FirstDate & """ already exists!"
                        Exit Sub
                        
                    ElseIf Sheet.Name = "Sheet2" & "." & FirstDate Then
                        MsgBox "The Worksheet " & """Sheet2." & FirstDate & """ already exists!"
                        Exit Sub
                        
                    End If
                    
                Next Sheets


        'Make a copy of the sheet and name it
            Sheets("Sheet1").Copy After:=Sheets("Sheet1")
            ActiveSheet.Name = "Downloaded.EDDs" & "." & FirstDate


End Sub

 

[JoeMo]

Here's a function you can call from a subroutine as in the example below:

Function SheetExists(wbName As String, shName As String) As Boolean
SheetExists = False
With Workbooks(wbName)
    For Each sh In .Sheets
        If sh.Name = shName Then
            SheetExists = True
            Exit For
        End If
     Next sh
End With
End Function

Call function from a subroutine:

Sub CheckIfSheetExists()
' see if a sheet named mySht exists in the activeworkbook
Dim mySht
If SheetExists(ActiveWorkbook.Name, "mySht") Then
    MsgBox "mySht is a sheet in the activeworkbook"
Else
    MsgBox "mySht is not a sheet in the activeworkbook"
End If
End Sub

 

[Peter_SSs]

Here is another option you could try in a copy of your workbook.
BTW, should the code also check that sheet "Downloaded.EDDs." & FirstDate does not already exist?

Sub Test()
  Dim FirstDate As String
 
  FirstDate = Format(Sheets("Sheet1").Range("E2").Value, "yyyy.mm.dd")
  If ShtExists("Sheet1." & FirstDate) Then
    MsgBox "The Worksheet " & """Sheet1." & FirstDate & """ already exists!"
  ElseIf ShtExists("Sheet2." & FirstDate) Then
    MsgBox "The Worksheet " & """Sheet2." & FirstDate & """ already exists!"
  Else
    Sheets("Sheet1").Copy After:=Sheets("Sheet1")
    Sheets(Sheets("Sheet1").Index + 1).Name = "Downloaded.EDDs." & FirstDate
  End If
End Sub


Function ShtExists(SheetName As String, Optional wb As Workbook) As Boolean
  On Error Resume Next
  ShtExists = (IIf(wb Is Nothing, ActiveWorkbook, wb).Sheets(SheetName).Name <> "")
  On Error GoTo 0
End Function

 

[jolivanes]

Change the "Next Sheets" into "Next Sheet"

 

[OilEconomist]

@JoeMo Thank you, that worked perfectly.

@Peter_SSs Thanks for your input. I will test once I get a chance.

@jolivanes Thanks for pointing out the typo.

 

[JoeMo]

@JoeMo Thank you, that worked perfectly.

@Peter_SSs Thanks for your input. I will test once I get a chance.

@jolivanes Thanks for pointing out the typo.

You are welcome - thanks for the reply.