Checking if a worksheet exists

Closed
Josh - May 2, 2012 at 05:04 PM
aquarelle Posts 7119 Registration date Saturday April 7, 2007 Status Moderator Last seen October 18, 2022 - May 3, 2012 at 12:33 PM
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

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
May 2, 2012 at 06:00 PM
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
0
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
0
aquarelle Posts 7119 Registration date Saturday April 7, 2007 Status Moderator Last seen October 18, 2022 491
May 3, 2012 at 12:33 PM
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
0