I am sure this isn't the best way to write this code, but I am not totally familiar with how to use a CASE statement to replace all these ANDs or if there is even a better option. The part of the code that has this is just a regular IF/THEN, with a fair amount of exclusions. It is listed here.



For Each sh In myWb.Worksheets
        If sh.Name <> "control" And sh.Name <> "E-mail Template" And sh.Name <> "UPCdsc" And sh.Name <> "UPCcost" And sh.Name <> "reference" Then
           Sheets(Array("control", "E-mail Template", "UPCdsc", "UPCcost", sh.Name)).Copy
                ActiveWorkbook.SaveAs Filename:=wbNm & " - " & sh.Name, _
                   FileFormat:=xlNormal, password:="", WriteResPassword:="", _
                   ReadOnlyRecommended:=False, CreateBackup:=False
                ActiveWorkbook.Close
        End If
    Next


I assume this will work for now, I am just trying to figure out a better way to write it in case there are more exclusions.



If you need the whole sub to see the variables, I have copied it below.




Sub SaveEachWSwINDEX()

    Dim myWb As Workbook
    Dim sh As Worksheet
    Dim myPathWhereToSave As String
    Dim wbNm   As String

    Set myWb = ActiveWorkbook

    myPathWhereToSave = "C:\Documents and Settings\tsarg00\Desktop\"
   
        wbNm = myPathWhereToSave & Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4)
   
    Application.ScreenUpdating = False
    For Each sh In myWb.Worksheets
        If sh.Name <> "control" And sh.Name <> "E-mail Template" And sh.Name <> "UPCdsc" And sh.Name <> "UPCcost" And sh.Name <> "reference" Then
           Sheets(Array("control", "E-mail Template", "UPCdsc", "UPCcost", sh.Name)).Copy
                ActiveWorkbook.SaveAs Filename:=wbNm & " - " & sh.Name, _
                   FileFormat:=xlNormal, password:="", WriteResPassword:="", _
                   ReadOnlyRecommended:=False, CreateBackup:=False
                ActiveWorkbook.Close
        End If
    Next
    Application.ScreenUpdating = True
    
End Sub