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?

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

On Wed, 2 Jul 2008 14:37:02 -0700, LostInNY
wrote:

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 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.

"Gord Dibben" wrote:

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

On Wed, 2 Jul 2008 14:37:02 -0700, LostInNY
wrote:

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?

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.

LostInNY wrote:

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.

"Gord Dibben" wrote:

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

On Wed, 2 Jul 2008 14:37:02 -0700, LostInNY
wrote:

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?



--

Dave Peterson

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


On Wed, 2 Jul 2008 18:28:00 -0700, LostInNY
wrote:

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.

"Gord Dibben" wrote:

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

On Wed, 2 Jul 2008 14:37:02 -0700, LostInNY
wrote:

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?