Need to add some code - Save tabs as file but exclude certain tabs

[bcurrey]

I have the code below that saves each tab as a new file. However, I have a few tabs that I don't want saved as files. Can someone help me on how to exclude specific tabs? I want to exclude on labeled "Original" and one labeled "Work". Thank you!

For sht = 1 To ThisWorkbook.Sheets.Count   
   DataCol = 1  'is there a specific column that will have data?
   If Worksheets(sht).Cells(11, DataCol) = "" Then GoTo DoNotSaveThisTab


    MyName_Start = "filepath\filename" & ThisWorkbook.Sheets(sht).Name & " " & Format(Date, "mmddyy")


 Sheets(sht).Select
      ActiveSheet.Copy
    With ActiveWorkbook
        .SaveAs MyName_Start & ".xls", FileFormat:=xlExcel8, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False
        .Close 0
    End With


Next sht

 

[Joe4]

Put everything inside of your For loop inside of an IF clause that checks the sheet name, i.e.

For sht = 1 To ThisWorkbook.Sheets.Count
    [COLOR=#ff0000]If (sht.Name <> "Original") And (sht.Name <> "Work") Then[/COLOR]
    '   Rest of your current code here
    [COLOR=#ff0000]End If[/COLOR]
Next sht

So what is in red indicates the new code to add.

 

[bcurrey]

Put everything inside of your For loop inside of an IF clause that checks the sheet name, i.e.

For sht = 1 To ThisWorkbook.Sheets.Count
    [COLOR=#ff0000]If (sht.Name <> "Original") And (sht.Name <> "Work") Then[/COLOR]
    '   Rest of your current code here
    [COLOR=#ff0000]End If[/COLOR]
Next sht

So what is in red indicates the new code to add.

Thanks Joe.


When I ran the code, it gave me an error. It highlighted your 1st row of new code and said "Object required".

 

[Joe4]

Issue with the sheet name references. Just set it up like you use later in your code.

    If (Sheets(sht).Name <> "Original") And (Sheets(sht).Name <> "Work") Then

 

[Norie]

Try this.

Dim sht As Worksheet
Dim MyName_Start As String
Dim I As Long
Dim DataCol As Long

    DataCol = 1  'is there a specific column that will have data?

    For I = 1 To ThisWorkbook.Sheets.Count

        Set sht = ThisWorkbook.Worksheets(I)

        Select Case sht.Name
            Case "Working", "Original"    ' add any more sheets to exclude
                ' do nothing
            Case Else

                If sht.Cells(11, DataCol) <> "" Then    '  GoTo DoNotSaveThisTab

                    MyName_Start = "filepath\filename" & sht.Name & " " & Format(Date, "mmddyy")
                    
                    sht.Copy

                    With ActiveWorkbook
                        .SaveAs MyName_Start & ".xls", FileFormat:=xlExcel8, _
                                Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
                                CreateBackup:=False
                        .Close 0
                    End With

                End If
                
        End Select

    Next I