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 <(E-Mail Removed)>
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 <(E-Mail Removed)>
> 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 <(E-Mail Removed)>
> > 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 <(E-Mail Removed)>
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 <(E-Mail Removed)>
>> 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?
>>
>>