Checking the existence of a sheet

[amitc4u]

Hi,

I have a Macro that paints cells in a designated sheet.
I need to add a primer check that the sheet really exist in the worksheet.
please see the attached file.
need to perform a check on the sheet name in cell E6.

Thank's

 

[Somendra Misra]

Hi Amit,

Try below code:

Sub Loop_Selection()

Dim sht, clr, crit, c

sht = Sheets("Design Sheet").Range("E6").Value
clr = Sheets("Design Sheet").Range("E7").Interior.ColorIndex
crit = Sheets("Design Sheet").Range("E5").Text
 sheetcount = ThisWorkbook.Worksheets.Count
 c = 0
 For i = 1 To sheetcount
    If Sheets(i).Name = sht Then
        c = 1
    End If
Next i

If c = 1 Then
    MsgBox "Sheet is there"
Else
    MsgBox "Sheet is not there"
End If
Dim cell As Range


Sheets(sht).Select


lc = Split(Range("IV3").End(xlToLeft).Address(), "$")(1)
   
lr = Range("B" & Rows.Count).End(xlUp).Row

Range("B3:" & lc & lr).Select

Dim rng As Range
Set rng = Application.Selection

For Each cell In rng

If cell.Value Like "*" + crit + "*" Then
cell.Interior.ColorIndex = clr
End If

Next

End Sub

Regards,

 

[Mike H..]

Hi,

I have a Macro that paints cells in a designated sheet.
I need to add a primer check that the sheet really exist in the worksheet.
please see the attached file.
need to perform a check on the sheet name in cell E6.

Thank's

Hi,

A simple way to test if a worksheet exists is this:-

Sub somesub()
Dim ws As Worksheet
On Error Resume Next
Set ws = Sheets(Sheets("Design Sheet").Range("E6").Value)
On Error GoTo 0
If ws Is Nothing Then
  MsgBox "Worksheet doesn't exist"
Else

  'Execute your code
End If

End Sub