Check sheet names and display MsgBox

[ilyaskazi]

following are the sheet names in the workbook...

chk-fares, fares, rules, zones, temp, sheet1, temp-zones
from the above given sheets, 3 sheets (names given below) are always constant....
fares, rules, zones

workbook may contain any number of sheets and by any names. but if any 1 or 2 or all the 3 constant sheets found in the workbook, then show msgbox as="yes ap-temp" else show msgbox as "not ap-temp"

[Richie(UK)]

Hi,

Please don't SHOUT when you post questions. Thanks
(Edit : I see the post has been amended for you)

You could try making a function that will return True or False depending upon whether the workbook contains one of the specified worksheets. Something like this perhaps:

Sub test()
    Dim varWSNames As Variant
varWSNames = Array("FARES", "RULES", "ZONES")
    MsgBox "Does the workbook contain one of the specified sheets?" & vbNewLine & _
        SheetsExist(varWSNames)
End Sub

Function SheetsExist(varSheetNames As Variant, Optional wbk As Workbook) As Boolean
    Dim lCnt As Long, ws As Worksheet
SheetsExist = False
    If wbk Is Nothing Then Set wbk = ActiveWorkbook
For lCnt = LBound(varSheetNames) To UBound(varSheetNames)
        On Error Resume Next
        Set ws = wbk.Worksheets(varSheetNames(lCnt))
        On Error GoTo 0
        If Not ws Is Nothing Then
            SheetsExist = True
            Exit For
        End If
    Next lCnt
End Function

HTH

[ilyaskazi]

some or the other way its working fine...thanku

let me check to my requirement and then I will be back.

[ilyaskazi]

Yes it is ok.
But now actually my need is to check if all the sheet exist which is stored in arrays.

At present your code is checking if any of the sheet exist. I want true answer only if all the sheets exist.

[Richie(UK)]

Hi,

Like this?

Sub test()
    Dim varWSNames As Variant
varWSNames = Array("FARES", "RULES", "ZONES")
    MsgBox "Does the workbook contain all of the specified sheets?" & vbNewLine & _
    AllSheetsExist(varWSNames)
End Sub
 
Function AllSheetsExist(varSheetNames As Variant, Optional wbk As Workbook) As Boolean
    Dim lCnt As Long, ws As Worksheet
AllSheetsExist = True
    If wbk Is Nothing Then Set wbk = ActiveWorkbook
For lCnt = LBound(varSheetNames) To UBound(varSheetNames)
        On Error Resume Next
        Set ws = wbk.Worksheets(varSheetNames(lCnt))
        On Error GoTo 0
        If ws Is Nothing Then
            AllSheetsExist = False
            Exit For
        End If
        Set ws = Nothing
    Next lCnt
End Function

HTH

[ilyaskazi]

Exactly,

Fantastic. Thanku.

[ilyaskazi]

if found true, how to import sheets in new workbook.

import only which matches the names containing in arrays.

[ilyaskazi]

ok i hv done myself. thanku..

[Zack Barresse]

Hi,

Also, a couple of other methods in our KB to test for sheets ...

http://www.vbaexpress.com/kb/getarticle.php?kb_id=187
http://www.vbaexpress.com/kb/getarticle.php?kb_id=420
http://www.vbaexpress.com/kb/getarticle.php?kb_id=389 (chart only)


(Hiya Richie!!)