VBA to check if a worksheet exists
VBA to check if a worksheet exists
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: VBA to check if a worksheet exists

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Milwaukee, WI
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Does anyone know the code for checking if a worksheet exists. The worksheets name will be the String "NewSht" followed by either the letter "L" or "B". I'm trying to check if the sheet exists, and if it does, overwrite all its cells. If it doesn't, I need to create a new sheet with that name. Thanks for the help.

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    If you are going to overwrite them anyway, Just use this:

    Application.DisplayAlerts = False
    On Error Resume Next
    Sheets("NewShtL").Delete
    Sheets("NewShtB").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    Sheets.Add().Name = "NewSht"


    But to check if sheet exists you would use

    Dim wsSheet As Worksheet
    On Error Resume Next
    Set wsSheet = Sheets("NewShtL")
    On Error GoTo 0
    If Not wsSheet Is Nothing Then
    MsgBox "I do exist"
    Else
    MsgBox "I do NOT exist"
    End If



  3. #3
    MrExcel MVP
    Colo's Avatar
    Join Date
    Mar 2002
    Location
    Kobe, Japan
    Posts
    1,456
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi. The following code is using Loop.
    Please try. Regards,

    Sub Test()
    Dim sh As Worksheet, flg As Boolean
    For Each sh In Worksheets
    If sh.Name Like "NewSht*" Then flg = True: Exit For
    Next
    If flg = True Then
    MsgBox "Found!"
    Else
    Sheets.Add.Name = "NewSht"
    End If
    End Sub

  4. #4
    Board Regular
    Join Date
    Jul 2006
    Posts
    70
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default very clever.

    setting the non-existent sheet as an object and then blowing through the errors is clever indeed.

    also learned from the use of wildcard * in the second suggestion.

  5. #5
    New Member
    Join Date
    Jun 2009
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to check if a worksheet exists

    Public Function WorksheetExists(ByVal WorksheetName As String) As Boolean

    On Error Resume Next
    WorksheetExists = (Sheets(WorksheetName).Name <> "")
    On Error GoTo 0

    End Function
    Last edited by Spencer Jobe; Jun 16th, 2009 at 04:25 PM.

  6. #6
    Board Regular
    Join Date
    Mar 2010
    Location
    Chichester, England
    Posts
    129
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to check if a worksheet exists

    I am having problems implementing the code to check whether a sheet already exists in my own macro - for some reason, the following code always seems to think that the new sheet name already exists, and asks whether I would like to overwrite it, even when the sheet in question patently does not exist. Below is a section of the macro, containing the relevant code:
    Code:
    Sub NewSheets()
    Dim agent As String
    Dim lastone As String
    Dim i As Integer
    Dim max As Integer
    Dim ws As Worksheet
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    max = InputBox("How Many Rows down the list do you wish to use?", "Enter a number to create that number of sheets")
    For i = 1 To max
        'for the first of the new sheets, the previous sheet is called template, by definition
        If i = 1 Then
                lastone = "Template"
        'otherwise, the last previous sheet name will be one higher in the list than the current
        Else
                lastone = Worksheets("Lists").Range("A1").Offset(i - 1, 0).Value
        End If
            'get the agent name
            agent = Worksheets("Lists").Range("A1").Offset(i, 0).Value
            'check whether a sheet with that name already exists
            On Error Resume Next
            Set ws = Sheets(agent)
        'if none with that name, create a new sheet
        If ws Is Nothing Then
                ActiveWorkbook.Sheets("Template").Copy after:=ActiveWorkbook.Sheets(lastone)
                ActiveSheet.Name = agent
        'if the sheet already exists, ask whether the user wants to replace it
        Else
                overW = MsgBox("This sheet already exists - Overwrite?", vbYesNoCancel, agent)
            If overW = vbYes Then
                    Sheets(agent).Delete
                    ActiveWorkbook.Sheets("Template").Copy after:=ActiveWorkbook.Sheets(lastone)
                    ActiveSheet.Name = agent
            ElseIf overW = vbNo Then
                    GoTo Skip
            ElseIf overW = vbCancel Then
                    Exit Sub
            End If
        End If
    I cannot work out which part I have got wrong - can anyone help?

    Thanks for looking at the post, and all replies are much appreciated!

  7. #7
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    15,702
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA to check if a worksheet exists

    Possibly you have not reset ws as nothing:
    Code:
            On Error Resume Next
            Set ws = Sheets(agent)
    Try amending to:
    Code:
            Set ws = Nothing
            On Error Resume Next
            Set ws = Sheets(agent)
    Also not that error handling will be left "on" and may mask other problems now. So also preferable is:
    Code:
            Set ws = Nothing
            On Error Resume Next
            Set ws = Sheets(agent)
            On Error GoTo 0

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  8. #8
    Board Regular
    Join Date
    Mar 2010
    Location
    Chichester, England
    Posts
    129
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile Thanks

    Thanks - worked perfectly. I will look up what "on error goto 0" actually does and thus try to avoid that in the future.

  9. #9
    New Member
    Join Date
    Jan 2012
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to check if a worksheet exists

    Hey Guys,

    I saw this thread and I wanted to show the code I came up with.

    I don't like "On Error Resume Next" command so I made the following function:


    Code:
    Sub Testing()
        Dim SheetName1, SheetName2 As String
        Dim Result As Boolean
        Dim i As Long
        
        
        SheetName = Array("laskgfasdfalskg", "Config")
        
        For i = 0 To UBound(SheetName)
            Result = WorksheetExists(SheetName(i))
            If Result = False Then
                MsgBox "Sheet name " & SheetName(i) & " doesn't exist!"
            Else
                MsgBox "Sheet name " & SheetName(i) & " does exist!"
            End If
        Next i
        
        
    End Sub
    
    
    
    Public Function WorksheetExists(ByVal WorksheetName As String) As Boolean
        
        Dim Sht As Worksheet
            
        WorksheetExists = False
            
        For Each Sht In ActiveWorkbook.Worksheets
            If Sht.Name = WorksheetName Then worksheetexits = True
        Next Sht
        
    End Function

  10. #10
    New Member
    Join Date
    Jan 2004
    Location
    New York
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to check if a worksheet exists

      
    Here is a working version

    Function WorksheetExists(ByVal WorksheetName As String) As Boolean
    Dim Sht As Worksheet
    For Each Sht In ThisWorkbook.Worksheets
    If Application.Proper(Sht.Name) = Application.Proper(WorksheetName) Then
    WorksheetExists = True
    Exit Function
    End If
    Next Sht
    WorksheetExists = False
    End Function

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com