How do I skip specific workbooks in my macro?

[ramkrau]

I've tried to look up how to do this, but with my Macro I keep getting errors and just can't figure it out...

The Macro is designed to automatically save each sheet as it's CSV file. I have a couple sheets at the beginning that I want it to skip. How do I make the Macro automatically skip specific sheets but process everything else? The names and quantity of the sheets that I DO want it to process are not consistent, but the ones I do want to skip remain the same...

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, "yyyy-mm-dd hh-mm-ss")
FolderName = xWb.Path & "" & "NA Test Cases " & DateString
MkDir FolderName
For Each xWs In xWb.Worksheets
    xWs.Copy
    If Val(Application.Version) < 12 Then
        FileExtStr = ".csv": FileFormatNum = 6
    Else
        Select Case xWb.FileFormat
            Case 51:
                FileExtStr = ".csv": FileFormatNum = 6
            Case 52:
                If Application.ActiveWorkbook.HasVBProject Then
                    FileExtStr = ".csv": FileFormatNum = 6
                Else
                    FileExtStr = ".csv": FileFormatNum = 6
                End If
            Case 56:
                FileExtStr = ".csv": FileFormatNum = 6
            Case Else:
                FileExtStr = ".csv": FileFormatNum = 6
        End Select
    End If
    xFile = FolderName & "" & Application.ActiveWorkbook.Sheets(1).Name & 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

 

[Joe4]

Welcome to the Board!

How can we identify which sheets to skip and which ones not to?
Are there specific names to these sheets? Or indexes?
Which is more, the number to skip, or the number to process?

If it is just a few to skip, and the names are static, you can use a simple IF statement right after your "FOR" loop line.
For example, if we wanted to skip Sheet1, Sheet2, and Sheet3, we could do it like this:

For Each xWs In xWb.Worksheets
    If (xWs.Name <> "Sheet1") and (xWs.Name <> "Sheet2") and (xWs.Name <> "Sheet3") Then
        'the rest of your code here
    End If
Next xWs

 

[Mentor82]

Hi
Try this code.

Sub SplitWorkbook()
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim xWs As Worksheet
Dim xWb As Workbook
Dim FolderName As String
Dim xNewWb as workbook
Application.ScreenUpdating = False
Set xWb = Application.ThisWorkbook
DateString = Format(Now, "yyyy-mm-dd hh-mm-ss")
FolderName = xWb.Path & "" & "NA Test Cases " & DateString
MkDir FolderName
For Each xWs In xWb.Worksheets
    If not(xWs.name like "*specific_chars_in_name_to_skip_worksheet*") then
    Workbooks.open
    Set xNewWb = activeworkbook
    xWs.Copy before:=xNewWb.worksheets(1)
    If Val(Application.Version) < 12 Then
        FileExtStr = ".csv": FileFormatNum = 6
    Else
        Select Case xWb.FileFormat
            Case 51:
                FileExtStr = ".csv": FileFormatNum = 6
            Case 52:
                If Application.ActiveWorkbook.HasVBProject Then
                    FileExtStr = ".csv": FileFormatNum = 6
                Else
                    FileExtStr = ".csv": FileFormatNum = 6
                End If
            Case 56:
                FileExtStr = ".csv": FileFormatNum = 6
            Case Else:
                FileExtStr = ".csv": FileFormatNum = 6
        End Select
    End If
    xFile = FolderName & "" & Application.ActiveWorkbook.Sheets(1).Name & FileExtStr
    Application.displayalerts=false
    Application.ActiveWorkbook.SaveAs xFile, FileFormat:=FileFormatNum
    Application.ActiveWorkbook.Close
     Application.displayalerts=true
False
End if
xWb.activate
Next
MsgBox "You can find the files in " & FolderName
Application.ScreenUpdating = True
Set xWb=nothing
Set xNewWb=nothing
End Sub

 

[ramkrau]

Sub SplitWorkbook()
'Updateby20140612
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, "yyyy-mm-dd hh-mm-ss")
FolderName = xWb.Path & "" & "NA Test Cases " & DateString
MkDir FolderName
For Each xWs In xWb.Worksheets
If (xWs.Name <> "NOT ME") Then
xWs.Copy
If Val(Application.Version) < 12 Then
FileExtStr = ".csv": FileFormatNum = 6
Else
Select Case xWb.FileFormat
Case 51:
FileExtStr = ".csv": FileFormatNum = 6
Case 52:
If Application.ActiveWorkbook.HasVBProject Then
FileExtStr = ".csv": FileFormatNum = 6
Else
FileExtStr = ".csv": FileFormatNum = 6
End If
Case 56:
FileExtStr = ".csv": FileFormatNum = 6
Case Else:
FileExtStr = ".csv": FileFormatNum = 6
End Select
End If
xFile = FolderName & "" & Application.ActiveWorkbook.Sheets(1).Name & 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



This gives me a compile error "Next without For"

 

[ramkrau]

and this one gives me a compile error "Argument not optional"

 

[Norie]

You can easily fix the first code by adding an End If.

Sub SplitWorkbook()
'Updateby20140612
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, "yyyy-mm-dd hh-mm-ss")
    
    FolderName = xWb.Path & "" & "NA Test Cases " & DateString
    
    MkDir FolderName
    
    For Each xWs In xWb.Worksheets
        If (xWs.Name <> "NOT ME") Then
            xWs.Copy
            If Val(Application.Version) < 12 Then
                FileExtStr = ".csv": FileFormatNum = 6
            Else
                Select Case xWb.FileFormat
                    Case 51:
                        FileExtStr = ".csv": FileFormatNum = 6
                    Case 52:
                        If Application.ActiveWorkbook.HasVBProject Then
                            FileExtStr = ".csv": FileFormatNum = 6
                        Else
                            FileExtStr = ".csv": FileFormatNum = 6
                        End If
                    Case 56:
                        FileExtStr = ".csv": FileFormatNum = 6
                    Case Else:
                        FileExtStr = ".csv": FileFormatNum = 6
                End Select
            End If
            xFile = FolderName & "" & Application.ActiveWorkbook.Sheets(1).Name & FileExtStr
            Application.ActiveWorkbook.SaveAs xFile, FileFormat:=FileFormatNum
            Application.ActiveWorkbook.Close False
        End If
    Next xWs
    
    MsgBox "You can find the files in " & FolderName
    Application.ScreenUpdating = True
    
End Sub

 

[Joe4]

Yes, when you added the new "IF" statement, you forgot to add the corresponding "End If".