If Not SheetExists statement to check for multiple sheets...

[sdoppke]

Im trying to modify my "If Not SheetExists" statement to allow for an array instead of checking for just one sheet. Hoping someone can help. Im going to include my function and macro. Sorry it quite a large marco

Function SheetExists(SheetName As String) As Boolean
' returns TRUE if the sheet exists in the active workbook
    SheetExists = False
    On Error GoTo NoSuchSheet
    If Len(Sheets(SheetName).Name) > 0 Then
        SheetExists = True
        Exit Function
    End If
NoSuchSheet:
End Function
Sub Upload_Schedule_to_SharePoint()
Dim wbname As Range
Dim wbfound As Boolean
Dim wb  As Workbook
ThisFile = Worksheets("MyStoreInfo").Range("C2")
Area = Worksheets("MyStoreInfo").Range("E8")
Region = Worksheets("MyStoreInfo").Range("F8")
District = Worksheets("MyStoreInfo").Range("C8")
M0nth = Worksheets("Dashboard").Range("O8")
Set wbname = ThisWorkbook.Sheets("MyStoreInfo").Range("C2")
    wbfound = False
    Application.ScreenUpdating = False
    
    If Not SheetExists("Week 1") Then
MsgBox "You have not imported the current Scheduling Template and created your schedules." & vbNewLine & "Press OK, then CLICK Schedule, then CLICK Get Current Scheduling Template.", vbInformation, "Upload Schedules to WFM Site"
    
    Else
    For Each sh In Sheets(Array("Week 1", "Week 2", "Week 3", "Week 4", "Week 5"))
    sh.Visible = True
    Next
    Set wb = Workbooks.Add
    Application.DisplayAlerts = False
    'save as Month, Schedule and store number
    wb.SaveAs Filename:=ThisWorkbook.Path & "/" & "" & ThisFile & ".xls"
    Application.DisplayAlerts = True
    
    Windows("WFMToolbackupLiteDateImport3.xlsm").Activate
    Sheets("Week 1").Select
    Cells.Select
    Selection.Copy
    For Each wb In Application.Workbooks
        If wb.Name = wbname.Text & ".xls" Then
            wbfound = True
            wb.Activate
            Exit For
        End If
    Next
    Sheets("Sheet1").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Name = "Week 1"
    
    Windows("WFMToolbackupLiteDateImport3.xlsm").Activate
    Sheets("Week 2").Select
    Cells.Select
    Selection.Copy
    For Each wb In Application.Workbooks
        If wb.Name = wbname.Text & ".xls" Then
            wbfound = True
            wb.Activate
            Exit For
        End If
    Next
    Sheets("Sheet2").Select
    ActiveSheet.Paste
    Sheets("Sheet2").Name = "Week 2"
    
    Windows("WFMToolbackupLiteDateImport3.xlsm").Activate
    Sheets("Week 3").Select
    Cells.Select
    Selection.Copy
    For Each wb In Application.Workbooks
        If wb.Name = wbname.Text & ".xls" Then
            wbfound = True
            wb.Activate
            Exit For
        End If
    Next
    Sheets("Sheet3").Select
    ActiveSheet.Paste
    Sheets("Sheet3").Name = "Week 3"
    
    Windows("WFMToolbackupLiteDateImport3.xlsm").Activate
    Sheets("Week 4").Select
    Cells.Select
    Selection.Copy
    For Each wb In Application.Workbooks
        If wb.Name = wbname.Text & ".xls" Then
            wbfound = True
            wb.Activate
            Exit For
        End If
    Next
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Week 4"
    Sheets("Week 4").Select
    ActiveSheet.Paste
    
    Windows("WFMToolbackupLiteDateImport3.xlsm").Activate
    Sheets("Week 5").Select
    Cells.Select
    Selection.Copy
    For Each wb In Application.Workbooks
        If wb.Name = wbname.Text & ".xls" Then
            wbfound = True
            wb.Activate
            Exit For
        End If
    Next
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Week 5"
    Sheets("Week 5").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    Sheets("Week 1").Select
    ActiveWorkbook.SaveAs Filename:= _
        "[URL]http://infonet.t-mobile.com/sites/retail/WFM/[/URL]" & Area & "/" & Region & "/" & District & "/" & M0nth & "Schedule" & ThisFile & ".xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    Windows("WFMToolbackupLiteDateImport3.xlsm").Activate
    For Each WkbkName In Application.Workbooks()
        If WkbkName.Name <> ThisWorkbook.Name Then WkbkName.Close
    Next
    For Each sh In Sheets(Array("Week 1", "Week 2", "Week 3", "Week 4", "Week 5"))
    sh.Visible = False
    Next
    Sheets("Dashboard").Select
    Application.ScreenUpdating = True
    MsgBox "Schedule Upload. Done. +1 for Doppke. (again...)", vbInformation, "Schedule Upload to SharePoint"
    End If
End Sub

Its this part that needs focus:

  If Not SheetExists("Week 1") Then
MsgBox "You have not imported the current Scheduling Template and created your schedules." & vbNewLine & "Press OK, then CLICK Schedule, then CLICK Get Current Scheduling Template.", vbInformation, "Upload Schedules to WFM Site"
    
    Else
    For Each sh In Sheets(Array("Week 1", "Week 2", "Week 3", "Week 4", "Week 5"))
    sh.Visible = True
    Next

I tried to just ad an array to this portion:

If Not SheetExists("Week 1") Then

but that makes it think the sheets are not there when they are?

so after much thought, im guessing I need to include the array, but modify the function some how?

Thanks in advance. I could do what i do with out this forum!

sd

 

[Norie]

All that was missing from the code I posted was the closing parenthesis.

That's it.