Check whether a sheet is present in a workbook

[Sagu]

Hello All,

I need some help in programming a macro.

I need to check whether a specific worksheet is present in a workbook.
If it is present, the macro follows one logic and if it does not it
follows another logic.

I tried referring to an cell value in that sheet,
If
ActiveWorkbook.Sheets("Alignment_Retail").Range("A1").Value = "Select"
Then

But, while running the code, if the sheet "Alignment_Retail" is not
present in the workbook, VBA immediately throws an "subscript out of
range" error.

Can some one please help me to draw out a different logic. All I need
is to validate, whether the sheet is present in the workbook or not. I
cannot use the worksheet count logic as the number of sheets can vary.

Hoping to hear from you!!!

Thanks in advance,
Sagar

 

[Jim Thomlinson]

Add this function
Public Function SheetExists(SName As String, _
Optional ByVal Wb As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If Wb Is Nothing Then Set Wb = ThisWorkbook
SheetExists = CBool(Len(Wb.Sheets(SName).Name))
End Function

and then use it something like this...

if sheetexists("Alignment_Retail", activeworkbook) then
ActiveWorkbook.Sheets("Alignment_Retail").Range("A1").Value = "Select"
end if

 

[ward376]

Here is one way:

Sub look()
On Error Resume Next
If Len(ThisWorkbook.Worksheets.Item("Alignment_Retail").Name) = 0
Then
On Error GoTo 0
MsgBox "The sheet isn't there."
Else
MsgBox "The sheet is there."
End If
End Sub

I've done this before, but now use a sheet management procedure in all
my projects.

Cliff Edwards

 

[Sagu]

It worked out perfectly..

Thanks a million for you help, Cliff !!

Thanks,
Sagar