Checking if a worksheet exists

[Closed]
Report
-
Posts
7098
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 10, 2021
-
Hello,

I'm back again.

I'm trying to determine if a worksheet exists in a workbook. If it does, I want to skip to the end of the code and do nothing. From what I understand, there will need to be a loop that looks at the name of each worksheet, then an If statement to check if the name is = to something. I think I've got the loop configured properly, but the If statement is where I'm getting caught up. The tricky part is I have the worksheets being named with the date as ActiveSheet.Name = Format(Today, "ddmmmyyyy").
--------------------------------------------------------------------------------
Dim wkSheet As WorkSheet

For Each wkSheet In ThisWorkbook.WorkSheets
If ______________ Then GoTo Open_File
Next wkSheet

<code>

Open_File:
----------------------------------------------------------------------------------

I appreciate the assistance and patience.
Thank you,
Josh

3 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
refer to the original thread where you discussed this. If you would notice, the sheet name is a variable. So you need to change the variable from hard coded date to use date and use format function
rizvisa1:
Thank you for the direction. I actually figured it out after you pointed me to "hardcoding" the date. Here's what I did.
---------------------------------------------------------------------
Dim shToday As String
Dim wkSheet As Worksheet
Dim Today as Date

Today = Date

shToday = Format(Today, "ddmmmyyyy")

For Each wkSheet In ThisWorkbook.Worksheets
If wkSheet.Name = shToday Then GoTo EndOfCode
Next wkSheet

<code>

EndOfCode:
-----------------------------------------------------------------------
Again, I appreciate the assistance.
Thank you,
Josh
Posts
7098
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 10, 2021
488
Hi,

Try and adapt this macro for your case :
Sub test()
Dim x As Worksheet
Dim ShToday As String

ShToday = Format(Now, "ddmmmyyyy")

On Error Resume Next
Set x = Worksheets(ShToday)
    If Err <> 0 Then MsgBox "the sheet does not exist" Else MsgBox "the sheet exists"
On Error GoTo 0
End Sub


Regards