CODEWITHSUNDEEP
excelvbacsvexport-to-csv
whoisceleste
whoisceleste

Reputation: 53

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

Upvotes: 1

Views: 433

Answers (1)

jclasley
jclasley

Reputation: 678

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

Upvotes: 1

Related Questions