VBA to check if Sheet name doesnt exists from single array

[mhdakhtar8]

Hi,

I'm in a situation where I need to check if some sheets already exist in a workbook which I need to run a macro on. That macro is designed for selected sheets. If any of the sheet is not existing, it will fail.

I want to put a control for VBA to check whether those sheets already exists or not.

Here's what I've reached to so far but It's giving me Compile error for sname.Length. The condition is I only want sheet name to be checked from Array for existence.

Sub Generate_Producitvity_Report()
Dim OW As Workbook, ws As Worksheet, sname() As String, i As Integer
sname = Array("COS REJECT", "APPROVAL SENT", "Pending Client Response", "Awaiting Four-Eye Check", "Awaiting RDS Approval", "SHEET6", "SHEET1")
Set OW = ActiveWorkbook
For Each ws In ThisWorkbook.Worksheets
ws.Select
With ws
    [COLOR="#FF0000"]For i = 0 To sname.Length[/COLOR]
        If ws.Name <> sname(i) Then
        MsgBox sname(i) & " sheet not found"
        End If
    Next i
End With
Next ws
End Sub

 

[V_Malkoti]

Try this:

Sub Generate_Producitvity_Report()
    Dim OW As Workbook, ws As Worksheet, sname As Variant, i As Integer
    sname = Array("COS REJECT", "APPROVAL SENT", "Pending Client Response", _
        "Awaiting Four-Eye Check", "Awaiting RDS Approval", "SHEET6", "SHEET1")
    Set OW = ActiveWorkbook
    
    For Each sN In sname
        On Error Resume Next
        Set ws = ThisWorkbook.Sheets(sN)
        If Err.Number > 0 Then Debug.Print sN & "sheet not found": Err.Clear
        On Error GoTo 0
    Next sN
End Sub

 

[RedBeard]

As far as I know, if you use String, you'll have to initialise all the posts one by one.
Declare it as Variant instead.
Also, your line in red, try using Ubound(sname) instead.

 

[Eric W]

Try this:

Sub Generate_Producitvity_Report()
Dim ws As Worksheet, i As Integer


    sname = Array("COS REJECT", "APPROVAL SENT", "Pending Client Response", "Awaiting Four-Eye Check", "Awaiting RDS Approval", "SHEET6", "SHEET1")


    On Error GoTo NotFound:


    For i = 0 To UBound(sname)
        Set ws = ActiveWorkbook.Sheets(sname(i))
    Next i
    Exit Sub


NotFound:
    MsgBox sname(i) & " sheet not found"
End Sub

 

[mhdakhtar8]

Thanks, its working without declaring. But I need to declare the

sN

. What should I declare it as?

For Each sN In sname

 

[mhdakhtar8]

Thanks @V_Malkoti, its working without declaring. But I need to declare the "sN". What should I declare it as?

For Each sN In sname

 

[V_Malkoti]

It should be declared as variant as sname is a variant.

Dim sN As Variant

 

[mhdakhtar8]

Thank, Exactly what I needed. You rock!!

 

[V_Malkoti]

Glad I could help.