Checking if a worksheet existsClosed

Question

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

Configuration: Windows 7 / Internet Explorer 8.0

rizvisa1 · Answer

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

Josh · Answer

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

aquarelle · Answer

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

Checking if a worksheet exists

3 responses

Similar discussions

Subscribe To Our Newsletter!