Macro that exports sheets to new workbooks except a speciffic sheet

Question

So I have a macro that exports each sheet into a new workbook. Now my problem is that I don't want to export a specific sheetname/(s) ("Source" sheet lets say) and when I add the code "If xWs.name<>"Source" then and add the else and end if I still get the "if without block if etc" error. I tried a lot of ways but is not functioning.

Can someone help ?

    Sub SplitWorkbook()
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim xWs As Worksheet
    Dim xWb As Workbook
    Dim FolderName As String
    Application.ScreenUpdating = False
    Set xWb = Application.ThisWorkbook
    DateString = Format(Now, "YYYYMMDD")
    DateString2 = Format(Now, " - MMMM YYYY")
    FolderName = xWb.Path & "\" & "Re'porting_" & DateString
    MkDir FolderName
    For Each xWs In xWb.Worksheets
    xWs.Copy
    If Val(Application.Version) < 12 Then
    FileExtStr = ".xls": FileFormatNum = -4143
    Else
    Select Case xWb.FileFormat
        Case 51:
            FileExtStr = ".xlsx": FileFormatNum = 51
        Case 52:
            If Application.ActiveWorkbook.HasVBProject Then
                FileExtStr = ".xlsm": FileFormatNum = 52
            Else
                FileExtStr = ".xlsx": FileFormatNum = 51
            End If
        Case 56:
            FileExtStr = ".xls": FileFormatNum = 56
        Case Else:
            FileExtStr = ".xlsb": FileFormatNum = 50
    End Select
    End If
    xFile = FolderName & "\" & Application.ActiveWorkbook.Sheets(1).Name & 
    DateString2 & FileExtStr
    Application.ActiveWorkbook.SaveAs xFile, FileFormat:=FileFormatNum
    Application.ActiveWorkbook.Close False
    Next
    MsgBox "You can find the files in " & FolderName
    Application.ScreenUpdating = True
    End Sub

Answer 1

I've taken your code and added the desired If...Then...Else statement. I've also formatted it with indentation and spacing between key steps in the code which makes it easier to read and identify when the code is doing/evaluating something new.

Sub SplitWorkbook()
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim xWs As Worksheet
Dim xWb As Workbook
Dim FolderName As String

Application.ScreenUpdating = False

Set xWb = Application.ThisWorkbook
DateString = Format(Now, "YYYYMMDD")
DateString2 = Format(Now, " - MMMM YYYY")
FolderName = xWb.Path & "\" & "Re'porting_" & DateString
MkDir FolderName

For Each xWs In xWb.Worksheets
    If Not xWs.Name = "Your Worksheet name to exclude" Then  'Change this string to suit your worksheets name
    xWs.Copy

        If Val(Application.Version) < 12 Then
            FileExtStr = ".xls": FileFormatNum = -4143
        Else
            Select Case xWb.FileFormat
                Case 51:
                    FileExtStr = ".xlsx": FileFormatNum = 51
                Case 52:
                    If Application.ActiveWorkbook.HasVBProject Then
                        FileExtStr = ".xlsm": FileFormatNum = 52
                    Else
                        FileExtStr = ".xlsx": FileFormatNum = 51
                    End If
                Case 56:
                    FileExtStr = ".xls": FileFormatNum = 56
                Case Else:
                    FileExtStr = ".xlsb": FileFormatNum = 50
            End Select
        End If

    xFile = FolderName & "\" & Application.ActiveWorkbook.Sheets(1).Name & DateString2 & FileExtStr
    Application.ActiveWorkbook.SaveAs xFile, FileFormat:=FileFormatNum
    Application.ActiveWorkbook.Close False
    Else
        'Go to next worksheet
    End If
Next xWs

MsgBox "You can find the files in " & FolderName
Application.ScreenUpdating = True
End Sub

This compiled and ran fine for me (except it was in an unsaved new workbook so the filepath basically didn't exist - so I commented out the MkDir and ...Save satatements).

I've also used If Not xWs = "..." rather than If xWs <> "...".