Flexible Array Set if it was deleted before

[Faridwahidi]

Hi,

I would like to write the array set of sheets name below BUT these sheets maybe deleted in the previous macro if it does not meet the criteria.

e.g. lets say, sheets("ScorePerformance_BB") was deleted before, the code may not be able to run.

how to resolve it

For Each ws In Sheets(Array("ScorePerformance_BB", _
                            "ScorePerformance_RCB", _ 
                            "ScorePerformance_CC", _
                            "ScorePerformance_RFS", _
                            "ScorePerformance_FTG", _
                            "ScorePerformance_MINVT", _
                            "ScorePerformance_CR"))

[jindon]

One way

    Dim e, ws As Worksheet
    For Each e In Array("ScorePerformance_BB", _
                "ScorePerformance_RCB", _
                "ScorePerformance_CC", _
                "ScorePerformance_RFS", _
                "ScorePerformance_FTG", _
                "ScorePerformance_MINVT", _
                "ScorePerformance_CR")
        On Error Resume Next
        Set ws = Sheets(e)
        On Error GoTo 0
        If Not ws Is Nothing Then
            ' your code here
        End If
    Next

[:) Sixthsense :)]

Sub ExampleCode()
Dim arNew() As String, Ws As Worksheet, ar As Variant, x As Integer

'This loop will check the availability of the sheets and load it in new array
For Each ar In Array("ScorePerformance_BB", "ScorePerformance_RCB", _
                            "ScorePerformance_CC", "ScorePerformance_RFS", _
                            "ScorePerformance_FTG", "ScorePerformance_MINVT", _
                            "ScorePerformance_CR", "Sheet1")
    On Error Resume Next
        Set Ws = Sheets(ar)
    On Error GoTo 0
    
    If Not Ws Is Nothing Then
        ReDim Preserve arNew(0 To x)
        arNew(x) = ar
        x = x + 1
    End If
Next ar

'Only Available Sheets Are loaded in arNew Array
For Each ar In arNew
    MsgBox ar
Next ar

End Sub

[Faridwahidi]

Hi Jindon & Sixthsense,


Thanks a lot.