Search code examples
excelvbacsvexport-to-csv

Save sheets as CSV - ignoring hidden sheets or sheets with macros


I need to save all sheets as CSV files with the exception of four hidden sheets ("chip", "play", "other", & "offers") and the sheet which contains the macro buttons ("Magic Buttons").

This code save all sheets. I do not know where to go from here (despite trial and error and reading some other Stack Overflow questions).

Sub SaveSheets()
'
' SaveSheets Macro
' Saves sheets as individual CSV files
'
Dim csvPath As String
csvPath = "C:\Daily Batch Files"
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each xWs In ThisWorkbook.Sheets
    xWs.Copy
    Application.ActiveWorkbook.SaveAs Filename:=csvPath & "\" & xWs.Name & ".csv", FileFormat:=xlCSV
    Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Solution

  • The conditions for saving need to go inside the loop.

    Using If...End If

    For Each xWs In ThisWorkbook.Sheets
        If xWs.Name <> "chip" And xWs.Name <> "play" And _
            xWs.Name <> "Other" And xWs.Name <> "offers" And _
            xWs.Name <> "Magic Buttons" Then
    
            xWs.Copy
            Application.ActiveWorkbook.SaveAs Filename:=csvPath & "\" & xWs.Name & ".csv", FileFormat:=xlCSV
            Application.ActiveWorkbook.Close False
        End If
    Next
    

    Using Select Case...End Select

    For Each xWs In ThisWorkbook.Sheets
        Select Case xWs.Name
            Case "chip", "play", "Other", "offers", "Magic Buttons"
            Case Else
                xWs.Copy
                Application.ActiveWorkbook.SaveAs Filename:=csvPath & "\" & xWs.Name & ".csv", FileFormat:=xlCSV
                Application.ActiveWorkbook.Close False
        End Select
    Next