Save specific sheets in a workbook as separate files using VBA

[ubergreen]

I found the code below and modified it to fit my needs, but the last step that I haven't been able to figure out is how to have it save only certain or specific sheets in my workbook. At the moment, it saves all of the sheets in the workbook.

   Public Sub SaveSheetsAsCsv()

   Dim WS As Excel.Worksheet
   Dim SaveToDirectory As String

   Dim CurrentWorkbook As String
   Dim CurrentFormat As Long
  
   Application.DisplayAlerts = False

   CurrentWorkbook = ThisWorkbook.FullName
   CurrentFormat = ThisWorkbook.FileFormat
  
   SaveToDirectory = "(folder location)"
  
   For Each WS In ThisWorkbook.Worksheets
  
   WS.SaveAs SaveToDirectory & WS.Name, xlCSV
   Next


End Sub

 

[ubergreen]

For clarification, do you want to save each sheet as a separate file?

Yea. I would like to save each sheet as a separate file. However, I want to only save certain/specific sheets.

For example, if my workbook has Sheet1, Sheet2, and Sheet3. I want to be able to have it only save Sheet1 and Sheet3 as a csv file when the code is ran.

 

[mumps]

Try:

Public Sub SaveSheetsAsCsv()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Dim WS As Worksheet, SaveToDirectory As String, fName As String
    SaveToDirectory = "(folder location)"
    For Each WS In Sheets(Array("Sheet1", "Sheet3"))
        WS.Copy
        fName = SaveToDirectory & WS.Name & ".csv"
        With ActiveWorkbook
            .SaveAs Filename:=fName, FileFormat:=xlCSV, CreateBackup:=False
            .Close , False
        End With
    Next WS
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

 

[ubergreen]

Try:

Public Sub SaveSheetsAsCsv()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Dim WS As Worksheet, SaveToDirectory As String, fName As String
    SaveToDirectory = "(folder location)"
    For Each WS In Sheets(Array("Sheet1", "Sheet3"))
        WS.Copy
        fName = SaveToDirectory & WS.Name & ".csv"
        With ActiveWorkbook
            .SaveAs Filename:=fName, FileFormat:=xlCSV, CreateBackup:=False
            .Close , False
        End With
    Next WS
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

Thanks. That did it. Surprisingly, while trying to find this solution, it was more common for people to want to simply save all of the sheets in a workbook as separate files and not only specific/select sheets.

 

[mumps]

You are very welcome.