Worksheets

[memberlogin]

How can I find out if an Excel worksheet exists before running a macro so as to avoid an error?

 

[bultimatem]

On Error Resume Next
ThisWorkbook.Sheets("sheet_name&quot.Activate
If Err <> 0 Then
MsgBox &quot;Sheet sheet_name does not exist&quot;
Exit Sub
End If
On Error GoTo 0

That should do it!

Good Luck,

Bryan Marble
IEWS
BAE Systems

 

[SkipVought]

Just one more thing... CLEAR the error...

Err.Clear

Bryan, are you in Nashua? Loved to hike the White Mts

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[memberlogin]

I am sorry if I mislead you but the worksheets are in different Workbooks so the line:-
ThisWorkbook.Sheets(&quot;sheet_name&quot.Activate
would not work!!

 

[bryanbayfield]

Option Explicit

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 19/06/2003 by Bryan Bayfield
'
Dim wkbIterate As Workbook, wksIterate As Worksheet, strList As String
strList = "?"
For Each wkbIterate In Application.Workbooks
    For Each wksIterate In wkbIterate.Sheets
        strList = strList & wksIterate.Name & "?"
        Next
    Next
Select Case InStr(strList, InputBox("Select a sheet name to search for"))
    Case 0
        Debug.Print "That sheet doesn't exist"
    Case Else
        Debug.Print "Sheet exists"
    End Select
End Sub