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

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
--
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("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
>

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

It worked out perfectly..

Thanks a million for you help, Cliff !!

Thanks,
Sagar


On Jun 9, 10:19*am, ward376 <cledwards1...@gmail.com> 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