Results 1 to 2 of 2
  1. #1
    Lounger
    Join Date
    Feb 2001
    Location
    North Carolina, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find specific Worksheet - if exists (Office 97 and XP)

    I need a macro that will look for a specific worksheet (like maybe "WS 04-25") within a workbook.
    If the sheet is found -- I do some stuff...
    But if NOT found -- I want it to tell me and do some other stuff.
    I know how to locate the sheet -- but I'm having trouble with the "not found" part.
    I'm sure it's not complicated -- but....
    Help!
    Phyl

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find specific Worksheet - if exists (Office 97 and XP)

    You can use two methods: loop through all sheets and see if you find one with the name you are looking for, or refer to the worksheet and trap the error that occurs if it doesn't exist.

    Function SheetExists(strName As String) As Boolean
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
    If ws.Name = strName Then
    SheetExists = True
    Exit For
    End If
    Next ws
    End Function

    or

    Function SheetExists(strName As String) As Boolean
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = ActiveWorkbook.Worksheets(strName)
    SheetExists = (Err = 0)
    End Function

    Use like this:

    If SheetExists("WS 04-25") Then
    ' Code if worksheet exists goes here
    Else
    ' Code if worksheet doesn't exist goes here
    End If

Posting Permissions

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