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

 

[mumps]

What are those certain sheets?

 

[ubergreen]

What are those certain sheets?

Sorry, it would just be Sheet1, Sheet2, and Sheet3 for example.

 

[mumps]

Try:

Public Sub SaveSheetsAsCsv()
    Application.ScreenUpdating = False
    Dim WS As Worksheet, SaveToDirectory As String
    Application.DisplayAlerts = False
    SaveToDirectory = "(folder location)"
    For Each WS In Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
        WS.SaveAs SaveToDirectory & WS.Name, xlCSV
    Next WS
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

 

[ubergreen]

Try:

Public Sub SaveSheetsAsCsv()
    Application.ScreenUpdating = False
    Dim WS As Worksheet, SaveToDirectory As String
    Application.DisplayAlerts = False
    SaveToDirectory = "(folder location)"
    For Each WS In Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
        WS.SaveAs SaveToDirectory & WS.Name, xlCSV
    Next WS
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

Thanks, that worked. Just a few questions so I can better understand what you did for my own future reference.

I am still fairly new to excel/vba, so my questions may be a bit basic.

I saw that you changed the dim WS from Excel.Worksheet to just Worksheet. Is there any difference to using either one?

I also saw that you added Applicaton.DisplayAlerts = False at the start and then used = True at the end. Is there a reason for this? I thought you would want to simply leave it as False and only need to have it used in the beginning?

 

[mumps]

Regarding your first question, there is no difference. It is just a shorter form. If you are referring to Application.Screenupdating=False, this prevents screen refreshing so it speeds up the macro. You should set it to True at the end to restore screen refreshing. Generally, when you set a command to False, you should re-set it to True at the appropriate time. I hope this makes sense.

 

[ubergreen]

Regarding your first question, there is no difference. It is just a shorter form. If you are referring to Application.Screenupdating=False, this prevents screen refreshing so it speeds up the macro. You should set it to True at the end to restore screen refreshing. Generally, when you set a command to False, you should re-set it to True at the appropriate time. I hope this makes sense.

Ah, got it. For the True/False, I was referring the below that's bolded.

Public Sub SaveSheetsAsCsv()
Application.ScreenUpdating = False
Dim WS As Worksheet, SaveToDirectory As String
Application.DisplayAlerts = False
SaveToDirectory = "(folder location)"
For Each WS In Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
WS.SaveAs SaveToDirectory & WS.Name, xlCSV
Next WS
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

 

[mumps]

That line of code prevents alert messages from popping up and should be used with caution because if the macro errors out, you won’t get any error messages. If possible, error checking should be included in the code instead of using that line. In any case, it should be set to True at some point.

 

[ubergreen]

Try:

Public Sub SaveSheetsAsCsv()
    Application.ScreenUpdating = False
    Dim WS As Worksheet, SaveToDirectory As String
    Application.DisplayAlerts = False
    SaveToDirectory = "(folder location)"
    For Each WS In Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
        WS.SaveAs SaveToDirectory & WS.Name, xlCSV
    Next WS
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

Wanted to revisit this, since it seems like I initially missed this issue or for some reason I have a minor typo somewhere that's causing me issues.

It seems like with this code, it saves everything as expected, except it is using the data/values from only the first sheet for all of the files that are being saved with the code.

 

[mumps]

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