Creating CSV for every Excel Tab

[LostInNY]

Hi all. I need a way to create a new CSV file for every Excel tab I have in
a workbook whenever a user saves the workbook. The workbook has tabs 1-20
and the data is from A1 to EP5000. When the workbook is updated and saved I
need to generate a new CSV that has the same name as the tab it originated
from. Any ideas?

 

[Gord Dibben]

Sub Make_New_Books()
Dim w As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each w In ActiveWorkbook.Worksheets
w.Copy
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path _
& "\" & w.Name, FileFormat:=xlCSV
ActiveWorkbook.Close
Next w
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

 

[LostInNY]

Gord this works great, but I have 2 more questions for you. First, how can I
get this macro to run when saving the workbook. Second, if I wanted to be
more specific for the CSVs created how can this be done. For example, have
tabs 1-20, but now I want to only create CSVs for tabs 1-4 and 18.

 

[Dave Peterson]

One way:

Option Explicit
Sub Make_New_Books()
Dim wCtr As Long
Dim w As Worksheet
Dim myNames As Variant

myNames = Array("Tab1", "tab2", "tab3", "tab18") 'add more
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For wCtr = LBound(myNames) To UBound(myNames)
Set w = Worksheets(myNames(wCtr))
w.Copy
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path _
& "\" & w.Name, FileFormat:=xlCSV
ActiveWorkbook.Close
Next wCtr
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Another option is to allow you to select the sheets manually (click on the first
tab and ctrl-click on the subsequent tabs). Then export each of those grouped
sheets.

Option Explicit
Sub Make_New_Books()
Dim w As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each w In ActiveWindow.SelectedSheets
w.Copy
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path _
& "\" & w.Name, FileFormat:=xlCSV
ActiveWorkbook.Close
Next w
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

It could give you a bit more flexibility to do things without changing the code.

 

[Gord Dibben]

To run the code when saving the workbook run it from BeforeSave event in
Thisworkbook.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim w As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each w In ActiveWorkbook.Worksheets
w.Copy
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path _
& "\" & w.Name & Range("A2").Value, FileFormat:=xlCSV
ActiveWorkbook.Close
Next w
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

See Dave's reply for changes in code to cover an array of sheets or manually
selected sheets.


Gord