How to check the Sheet name exist before running Macro code

  • Thread starter Thread starter tlee
  • Start date Start date
T

tlee

Hello all,

Could anyone know how to check the sheet name (e.g. "mySheet") exist before
running the rest macro code ?

Thanks,

tlee
 
Copy the below function and use that in code as below

Sub Macro()
'If sheet do not exist then exit procedure
If Not IsSheetExists("mySheet") Then Exit Sub

End Sub


Function IsSheetExists(strSheet As String) As Boolean
On Error Resume Next
Dim ws As Worksheet
Set ws = Sheets(strSheet)
If Not ws Is Nothing Then IsSheetExists = True
End Function
 
Hi Jacob,

Thank you very much for your help !

tlee

-----------------------------------------------------------------------------------------------------------
 
Try a function like the following:

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


Set SheetName to the name of the worksheet to test, and set WB to the
workbook in which the sheet might exist. If you omit the WB parameter,
the code looks in the workbook that contains the code:

If SheetExists("Sheet5") = True Then
' do something
End If

' OR

If SheetExists("Sheet5",Workbooks("Book4.xls")) = True Then
' do something
End If

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]
 
Hello Chip,

Thank you so much for giving me alternative.

tlee
Try a function like the following:

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


Set SheetName to the name of the worksheet to test, and set WB to the
workbook in which the sheet might exist. If you omit the WB parameter,
the code looks in the workbook that contains the code:

If SheetExists("Sheet5") = True Then
' do something
End If

' OR

If SheetExists("Sheet5",Workbooks("Book4.xls")) = True Then
' do something
End If

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]




Hello all,

Could anyone know how to check the sheet name (e.g. "mySheet") exist
before
running the rest macro code ?

Thanks,

tlee
Click to expand...
Click to expand...
 
Back
Top