VBA to call either of two macros based on whether workbook contains a specific sheet name

[drewcarpenter2001]

I have the following code to call either of two macros for each looped workbook based on whether the individual workbooks contain a tab called "Summary", which is an upstream system default for certain of the files. The logical expression for the first call does not seem to be functioning - it is bypassing and running the second macro for all workbooks. Help please!

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name = "Summary" Then
Call Finance_Close_Summary
Else
Call Finance_NonSummary
End If
Next ws

 

[bebo021999]

"For each" stop at 1st sheet only, then follow Call 1 or Call 2. from sheet2, never been looked into.
Try to set k as "counting variable" like this"

For Each sh In ThisWorkbook.Sheets
    If sh.Name = "Summary" Then
    Call Finance_Close_Summary
    Exit Sub
    Else
    k = k + 1
    End If
Next
If k > 0 Then Call Finance_NonSummary
End Sub

Sub test()

 

[drewcarpenter2001]

Thank you - it is still unable to correctly identify if a Summary tab exists and run the correct macro. I'm wondering at this point if the "real" name of the tab is the issue. I've shown below how it appears in the Objects library - you can see it is identified there as "Sheet7 (Summary)".

 

[Fluff]

You are not looping through any workbooks, you are only looking at the sheets in the workbook that contains the code.
Can you please post the rest of your code.

 

[drewcarpenter2001]

Sub LoopAllExcelFilesInFolder()

Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog

  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual

  Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

    With FldrPicker
      .Title = "Select A Target Folder"
      .AllowMultiSelect = False
        If .Show <> -1 Then GoTo NextCode
        myPath = .SelectedItems(1) & "\"
    End With

NextCode:
  myPath = myPath
  If myPath = "" Then GoTo ResetSettings

  myExtension = "*.xls*"

  myFile = Dir(myPath & myExtension)

  Do While myFile <> ""
      Set wb = Workbooks.Open(Filename:=myPath & myFile)
   
     Dim sh As Worksheet
    For Each sh In ThisWorkbook.Sheets
    If sh.Name = "Summary" Then
    Call Finance_Close_Summary
    Exit Sub
    Else
    k = k + 1
    End If
Next
If k > 0 Then Call Finance_NonSummary
     
      wb.Close SaveChanges:=True
     
      DoEvents

      myFile = Dir
  Loop

  MsgBox "Task Complete!"

ResetSettings:
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

 

[Fluff]

Ok, try it like this

   Dim sh As Worksheet
   Do While myFile <> ""
      Set wb = Workbooks.Open(FileName:=myPath & myFile)
      If ShtExists("Summary", wb) Then
         Call Finance_Close_Summary
      Else
         Call Finance_NonSummary
      End If
     
      wb.Close SaveChanges:=True
     
      DoEvents

      myFile = Dir
  Loop

and add this to a standard module

Public Function ShtExists(ShtName As String, Optional Wbk As Workbook) As Boolean
    If Wbk Is Nothing Then Set Wbk = ActiveWorkbook
    On Error Resume Next
    ShtExists = (LCase(Wbk.Sheets(ShtName).Name) = LCase(ShtName))
    On Error GoTo 0
End Function

 

[drewcarpenter2001]

That works - thank you!

 

[Fluff]

Glad we could help & thanks for the feedback.