How to check if a sheet exists ?

J

JLGWhiz

Courtexy Chip Pearson:

Function SheetExists(SheetName As String, _
Optional WB As Workbook) As Boolean
Dim W As Workbook
If WB Is Nothing Then
Set W = ActiveWorkbook
Else
Set W = WB
End If
On Error Resume Next
SheetExists = CBool(Len(W.Worksheets(SheetName).Name))
End Function




Title says it all.
Luc
 
M

Mike H

Hi,

I'd have a seperate fumction and call it to test for the existance of a sheet

Sub nn()
MsgBox SheetThere("sheet3")
End Sub


Function SheetThere(ShtName As String) As Boolean
Dim x
On Error GoTo GetMeOut
x = ActiveWorkbook.Sheets(ShtName).Name
SheetThere = True
Exit Function

GetMeOut:
SheetThere = False
End Function


Mike
 
R

Rick Rothstein

Here is a shorter version of your "on error" method for this function...

Function SheetThere(ShtName As String) As Boolean
On Error Resume Next
SheetThere = Len(ActiveWorkbook.Sheets(ShtName).Name)
End Function
 
V

Vivek Samapra

Dude ! ! ! ! I LOVE YOU ! ! ! ! I Have searched over the internet for 3 hours.. Only yours worked the best.. Im going to share this to everyone..
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

When a sheet is copied, i want also that the Sub's whithin this sheet is copied to the new sheet 2
How do i print all visible sheets ? 6
Case select returning error when cell contains #N/A : how must i avoid this error 2
Append Crosstab Query to Existing Table 0
Replacing a phrase with carriage returns 1
How do i check if the a named range is empty 3
Automatically insert "Continued" in footer based on existing STYLEREF 2
Re-title multiple MS Project .mpp files at once 0

Top