CODEWITHSUNDEEP
vbaexcel
Jigar Rathod
Jigar Rathod

Reputation: 49

create new workbook for each worksheet except for specific worksheets

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.

Upvotes: 1

Views: 817

Answers (2)

Bodo
Bodo

Reputation: 172

Let's check your expression:

If Sheet.Name <> "Overall" Or Sheet.Name <> "Staff" Then

Sheet.Name == "Overall" will be false or true what results in true

Sheet.Name == "Staff" will result in true or false what results in true

Sheet.Name == "Something else" will result in true or true what results in true

I think you liked to have and not or

If Sheet.Name <> "Overall" And Sheet.Name <> "Staff" Then

Sheet.Name == "Overall" will be false and true what results in false

Sheet.Name == "Staff" will result in true and false what results in false

Sheet.Name == "Something else" will result in true and true what results in true

Upvotes: 2

Shai Rado
Shai Rado

Reputation: 33682

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.

Upvotes: 3

Related Questions