title: Excel & VBA: efficiently save multiple worksheets to CSV
body: <p>Assume that I have a workbook <code>MyFile.xlsm</code> with some worksheets (e.g. <code>SheetA</code> and <code>SheetB</code>) and I want to export the content of the worksheets into individual CSV files if the workbook is saved. (My question is similar to <a href="https://stackoverflow.com/q/37037934/6361813">this one</a>, but addresses multiple worksheets.) I came up with the code below, which works but takes some time since a new workbook is created via <code>ws.Copy</code> for each worksheet).</p>
  <p>Is there a more <strong>efficient</strong> way to do this? Originally, I wanted to use the method <code>Workbook.SaveCopyAs</code>, but the latter does not allow saving to CSV. Moreover, I could not figure out how to use <code>SaveAs</code> without copying, since without copying <code>SaveAs</code> activates the newly saved CSV file as the current workbook (and therefore messes up the name and the other worksheets). So, what is the proper way of exporting multiple worksheets to CSV files?</p>
  <pre><code>Private Sub Workbook_AfterSave(ByVal Success As Boolean)
  If Success = True Then
      For Each ws In ThisWorkbook.Worksheets
  
          With Application
              .EnableEvents = False      ' prevent repeatedly calling this routine
              .DisplayAlerts = False     ' suppress prompt for overwriting existing CSV files
              .ScreenUpdating = False    ' speed up and prevent screen flickering
          End With
  
          Filename = Replace(ActiveWorkbook.FullName, &quot;.xlsm&quot;, &quot;&quot;) &amp; &quot;_&quot; &amp; ws.Name &amp; &quot;.csv&quot;
          ws.Copy
          ActiveWorkbook.SaveAs Filename:=Filename, FileFormat:=xlCSVUTF8, Local:=True
          ActiveWorkbook.Close
  
          With Application
              .EnableEvents = True
              .DisplayAlerts = True
              .ScreenUpdating = True
          End With
  
      Next
      
  End If
  End Sub
  </code></pre>