Checking for a specific sheet in a workbook

[Joe9238]

Hi, below you can see a part of my code that will do stuff with a workbook so long as it is an xls file. The code searches for the sheet named 'quote' and then does stuff to that sheet. I would like to change the code to skip the file if the workbook does not have the page 'quote'.

If InStr(1, CodeNames(i, 1), ".xls") > 0 Then        If Not WorkbookOpen(CStr(Split(CodeNames(i, 1), "\")(UBound(Split(CodeNames(i, 1), "\"))))) Then
            Set wbTarget = Workbooks.Open(CodeNames(i, 1))
            With wbTarget.Worksheets("Quote")
                ary(0) = .Range("B7")
                ary(1) = .Range("B8")
                ary(2) = .Range("B11")
                ary(3) = .Range("B13")
            End With
        Else

Another feature I'd like added but is not necessary is the possibility for the sheet to be under a different name other than quote such as 'penguin'. Ideally, I'd like a small list of names the sheet could be. Of course though, if you don't know of the top of your head then don't bother with it.
Thanks in advance

 

[mumps]

This macro will skip the files named in the array. You can modify the array to suit your needs. You will also need to insert the rest of your code where appropriate.

Sub Test()
    Dim ws As Worksheet
    Dim shArray As Variant
    shArray = Array("Quote", "Sheet1", "Sheet2", "Sheet3")
    Dim i As Long
    Set wbTarget = Workbooks.Open(CodeNames(i, 1))
    For i = LBound(shArray) To UBound(shArray)
        On Error Resume Next
        Set ws = Sheets(shArray(i))
        On Error GoTo 0
        If ws Is Nothing Then
            MsgBox "Sheet " & Sheets(shArray(i)) & " does not exist."
        Else
            With wbTarget.Sheets(shArray(i))
                ary(0) = .Range("B7")
                ary(1) = .Range("B8")
                ary(2) = .Range("B11")
                ary(3) = .Range("B13")
            End With
        End If
    Next i
End Sub

 

[Joe9238]

Thanks for the reply. I am finding a problem with this code because 'for i' is already in use elsewhere in the code. Are there any alternatives?

 

[mumps]

Instead of an "i" you can use any letter. Also, I didn't explain properly what the code will do. It will check to see if the sheets in the array exist and if they do, then process the

With wbTarget.Sheets(shArray(i))

part of the code. Are you getting an error message when you try the code? If so, what is the error and which line of code is highlighted when you click 'Debug'?