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

S

sdoppke

Well-known Member
Joined
Jun 10, 2010
Messages
647
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 :(

Code: 
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:
Code: 
  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:
Code: 
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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
If you mean you want to check that all the worksheets in the array exist then you don't need to do anything with the function.

What you need to do is loop through the array and test for the existence of each sheet.

If one of the sheets isn't found then do whatever you need to do.

Here's one way you can go through the array and see if the sheets exist.
Code: 
Dim I As Long
Dim arrShts
 
arrShts = Array("Week 1", "Week 2", "Week 3", "Week 4", "Week 5")
 
For I = LBound(arrShts) To UBound(arrShts)
   If Not SheetExists(arrShts(I) Then
     Msgbox "You haven't done something.
     Exit For
   End If
 
Next I
 
Upvote 0
Norie said:
If you mean you want to check that all the worksheets in the array exist then you don't need to do anything with the function.

What you need to do is loop through the array and test for the existence of each sheet.

If one of the sheets isn't found then do whatever you need to do.

Here's one way you can go through the array and see if the sheets exist.
Code: 
Dim I As Long
Dim arrShts
 
arrShts = Array("Week 1", "Week 2", "Week 3", "Week 4", "Week 5")
 
For I = LBound(arrShts) To UBound(arrShts)
   If Not SheetExists(arrShts(I) Then
     Msgbox "You haven't done something.
     Exit For
   End If
 
Next I
Click to expand...

Thank you, thank you for the help.

im getting a sytax error on line:
Code: 
If Not SheetExists(arrShts(I) Then

am i missing a ")"?

sd
 
Upvote 0
Norie said:
If you mean you want to check that all the worksheets in the array exist then you don't need to do anything with the function.

What you need to do is loop through the array and test for the existence of each sheet.

If one of the sheets isn't found then do whatever you need to do.

Here's one way you can go through the array and see if the sheets exist.
Code: 
Dim I As Long
Dim arrShts
 
arrShts = Array("Week 1", "Week 2", "Week 3", "Week 4", "Week 5")
 
For I = LBound(arrShts) To UBound(arrShts)
   If Not SheetExists(arrShts(I) Then
     Msgbox "You haven't done something.
     Exit For
   End If
 
Next I
Click to expand...


Morning everyone :) anyone have an idea why i would be getting a Compile Error:Sytax Error with what Norie recommended here? I played with the ")" with n o luck.

sd
 
Upvote 0
Hello,

This is the sheet exist function I use:

<font face=Courier New><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> SheetExists(sname) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br><SPAN style="color:#007F00">'   Returns TRUE if sheet exists in the active workbook</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN><br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> x = ActiveWorkbook.Sheets(sname)<br>    <SPAN style="color:#00007F">If</SPAN> Err = 0 <SPAN style="color:#00007F">Then</SPAN> SheetExists = True _<br>        <SPAN style="color:#00007F">Else</SPAN> SheetExists = <SPAN style="color:#00007F">False</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>

I see it only takes one argument...

I do think the closing paren. is needed.

I am not for certain, however maybe try:

Code: 
Dim I As Long
Dim arrShts[COLOR=red]()[/COLOR]
 
arrShts = Array("Week 1", "Week 2", "Week 3", "Week 4", "Week 5")
 
For I = LBound(arrShts) To UBound(arrShts)
   If Not SheetExists(arrShts(I)[COLOR=red])[/COLOR] Then
     Msgbox "You haven't done something.
     Exit For
   End If
 
Next I
 
Upvote 0
I have tested to find the top red pair of parenthesis from my earlier post #5 was not needed.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> a()<br><br><SPAN style="color:#00007F">Dim</SPAN> I <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> arrShts<br> <br>arrShts = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4")<br> <br><SPAN style="color:#00007F">For</SPAN> I = <SPAN style="color:#00007F">LBound</SPAN>(arrShts) <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(arrShts)<br>   <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> SheetExists(arrShts(I)) <SPAN style="color:#00007F">Then</SPAN><br>     MsgBox "You haven't done something."<br>     <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">For</SPAN><br>   <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    MsgBox arrShts(I)<br><SPAN style="color:#00007F">Next</SPAN> I<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 
Upvote 0
Try this, AllSheetsExist will accept either a string or and array of strings as its argument.

Code: 
Function AllSheetsExist(sheetNames As Variant) As Boolean
    On Error Resume Next
    AllSheetsExist = Not (ThisWorkbook.Sheets(sheetNames).Parent Is Nothing)
    On Error GoTo 0
End Function

Sub test()
    MsgBox AllSheetsExist(Array("Sheet1", "Sheet3"))

    MsgBos AllSheetsExist("Sheet34")
End Sub
 
Upvote 0
repairman615 said:
Hello,

This is the sheet exist function I use:

Public Function SheetExists(sname) As Boolean
' Returns TRUE if sheet exists in the active workbook
Dim x As Object
On Error Resume Next
Set x = ActiveWorkbook.Sheets(sname)
If Err = 0 Then SheetExists = True _
Else SheetExists = False
End Function

I see it only takes one argument...

I do think the closing paren. is needed.

I am not for certain, however maybe try:

Code: 
Dim I As Long
Dim arrShts[COLOR=red]()[/COLOR]
 
arrShts = Array("Week 1", "Week 2", "Week 3", "Week 4", "Week 5")
 
For I = LBound(arrShts) To UBound(arrShts)
   If Not SheetExists(arrShts(I)[COLOR=red])[/COLOR] Then
     Msgbox "You haven't done something.
     Exit For
   End If
 
Next I
Click to expand...


When i modified my Macro with your function, I was good to go!! Thanks a tone for help.

Thanks everyone :)

sd
 
Upvote 0
Your welcome. Glad to help :)

mikerickson said:
Try this, AllSheetsExist will accept either a string or and array of strings as its argument.

Code: 
Function AllSheetsExist(sheetNames As Variant) As Boolean
    On Error Resume Next
    AllSheetsExist = Not (ThisWorkbook.Sheets(sheetNames).Parent Is Nothing)
    On Error GoTo 0
End Function
 
Sub test()
    MsgBox AllSheetsExist(Array("Sheet1", "Sheet3"))
 
    MsgBos AllSheetsExist("Sheet34")
End Sub
Click to expand...

This is very nice!
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Creating new sheets from template sheet plugging data into new sheets with particular criterias
Replies
1
Views
710
mumps
mumps
D
  • Solved
Use the same macro on a weekly file which contains date info
Replies
1
Views
449
John_w
J
willow1985
  • Solved
VBA Run-time error 91 - Object variable or With block variable not set when trying to refresh data/Sleep
Replies
4
Views
371
willow1985
willow1985
E
  • Question Question
Filtering values from data source and pasting it into a second workbook
2
Replies
17
Views
1K
kevin9999
K
B
  • Solved
VBA is there a way to speed up?
Replies
4
Views
414
RoryA
RoryA

Forum statistics

Threads
1,224,527
Messages
6,179,331
Members
452,907
Latest member
Roland Deschain

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top