create new workbook for each worksheet except for specific worksheets

Question

I've a workbook with multiple sheets which are to be divided into individual site wise sheets but the workbook also have few worksheets which are not required to create a new workbook.

For E.g. Sheet1 = Overall Sheet2 = Staff Sheet3 = Site1 Sheet4 = Site2....

Now I want to create new workbooks for Sheet3 (i.e. Site1) & Sheet4 (i.e. Site2)

If i just want to exclude One of the two sheets(Overall & Staff) its working fine but when i try to exclude both the sheets, my if condition doesn't seem to work properly.

Below is the code

Option Explicit


Sub SaveShtsAsBook()
Dim Sheet As Worksheet, SheetName$, MyFilePath$, N&
MyFilePath$ = ActiveWorkbook.Path & "\" & _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)

With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
     '      End With
    On Error Resume Next '<< a folder exists
    MkDir MyFilePath '<< create a folder
    For Each Sheet In Worksheets
        If Sheet.Name <> "Overall" Or Sheet.Name <> "Staff" Then
        Sheet.Copy
            With ActiveWorkbook
                With .ActiveSheet
                    [A1].Select
                    SheetName = ActiveSheet.Name
                End With
                .SaveAs Filename:=MyFilePath _
                & "\" & SheetName & ".xlsx"
                .Close SaveChanges:=True
            End With
            .CutCopyMode = False
        End If
    Next
End With
Sheet1.Activate
End Sub

P.S. The If Statement with an OR condition is causing the problem. The syntax is right, but I'm not able to understand when the if condition has an OR condition with it, why it does not check the condition.

Change the or to an and if you want to exclude both the overall sheet and the staff sheet. Hopefully you can see the issue, you need it to pass both conditions, not just one. — Zerk

Shai Rado Shai Rado · Accepted Answer · 2017-10-24T10:58:01

Try switching to Select Case, I think it's easier to understand, also you will have more flexibility in the future to add more sheet names in it.

'For Each Sheet In Worksheets
For Each Sheet In ThisWorkbook.Worksheets ' Safer way to qualify the worksheets with the workbook where this code lies

    Select Case Sheet.Name
        Case "Overall", "Staff"
            ' do nothing

        Case Else
            Sheet.Copy
            With ActiveWorkbook
                With .ActiveSheet
                    [A1].Select
                    SheetName = ActiveSheet.Name
                End With
                .SaveAs Filename:=MyFilePath _
                & "\" & SheetName & ".xlsx"
                .Close SaveChanges:=True
            End With
            .CutCopyMode = False

    End Select
Next

Note: I would change Sheet as a name for your worksheet object, and use soemthing like Sht or ws.

create new workbook for each worksheet except for specific worksheets

2 Answers