Check whether a sheet is present in a workbook
 

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("A 1").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

Check whether a sheet is present in a workbook
 

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("A 1").Value = "Select"
end if
--
HTH...

Jim Thomlinson


"Sagu" wrote:

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("A 1").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

Check whether a sheet is present in a workbook
 

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

Check whether a sheet is present in a workbook
 

It worked out perfectly..

Thanks a million for you help, Cliff !!

Thanks,
Sagar


On Jun 9, 10:19*am, ward376 wrote:
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