How do I copy a worksheet from a workbook as csv file

[Husker87]

I have a workbook with several worksheets. I want to record a macro that
will save one tab, "CSV Data" as its own csv file to another location. Any
ideas?

[Ron de Bruin]

Try this one to save the file in C:\

Sub Sheet_CSV_File()
Dim wb As Workbook
Dim strdate As String
Dim Fname As String

strdate = Format(Now, "dd-mm-yy h-mm-ss")

Fname = "C:\Part of " & ThisWorkbook.Name _
& " " & strdate & ".csv"

Application.ScreenUpdating = False
Sheets("CSV Data").Copy
Set wb = ActiveWorkbook
With wb
.SaveAs Fname, FileFormat:=xlCSV
.Close False
End With
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Husker87" <Husker87@discussions.microsoft.com> wrote in message news:20592323-BE14-4FC2-9DB1-D8795A3D1AC3@microsoft.com...
>I have a workbook with several worksheets. I want to record a macro that
> will save one tab, "CSV Data" as its own csv file to another location. Any
> ideas?

[Husker87]

Short follow up... btw this worked great. I changed the C: to A: so I could
save it to a disk. Here is my follow-up... can I change your code that
names the file to include the content of a cell on the CSV Data sheet? For
example the user has to enter a unique number into a cell "E1" on the CSV
Data worksheet. I would like the name of the CSV file to start with that
number in "E1" then followed by the date like you wrote it. Is that
possible?

"Ron de Bruin" wrote:

> Try this one to save the file in C:\
>
> Sub Sheet_CSV_File()
> Dim wb As Workbook
> Dim strdate As String
> Dim Fname As String
>
> strdate = Format(Now, "dd-mm-yy h-mm-ss")
>
> Fname = "C:\Part of " & ThisWorkbook.Name _
> & " " & strdate & ".csv"
>
> Application.ScreenUpdating = False
> Sheets("CSV Data").Copy
> Set wb = ActiveWorkbook
> With wb
> .SaveAs Fname, FileFormat:=xlCSV
> .Close False
> End With
> Application.ScreenUpdating = True
> End Sub
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "Husker87" <Husker87@discussions.microsoft.com> wrote in message news:20592323-BE14-4FC2-9DB1-D8795A3D1AC3@microsoft.com...
> >I have a workbook with several worksheets. I want to record a macro that
> > will save one tab, "CSV Data" as its own csv file to another location. Any
> > ideas?
>
>
>

[Ron de Bruin]

Hi

First of all never save to a floppy this way.
Always save to C:\ and copy manual to A


Try this

Sub Sheet_CSV_File_2()
Dim wb As Workbook
Dim strdate As String

strdate = Format(Now, "dd-mm-yy h-mm-ss")

Application.ScreenUpdating = False
Sheets("CSV Data").Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "C:\" & wb.Sheets(1).Range("E1") & " " & _
strdate & ".csv", FileFormat:=xlCSV
.Close False
End With
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Husker87" <Husker87@discussions.microsoft.com> wrote in message news:2FB79CE4-C691-475C-AA3D-38B1D2087201@microsoft.com...
> Short follow up... btw this worked great. I changed the C: to A: so I could
> save it to a disk. Here is my follow-up... can I change your code that
> names the file to include the content of a cell on the CSV Data sheet? For
> example the user has to enter a unique number into a cell "E1" on the CSV
> Data worksheet. I would like the name of the CSV file to start with that
> number in "E1" then followed by the date like you wrote it. Is that
> possible?
>
> "Ron de Bruin" wrote:
>
>> Try this one to save the file in C:\
>>
>> Sub Sheet_CSV_File()
>> Dim wb As Workbook
>> Dim strdate As String
>> Dim Fname As String
>>
>> strdate = Format(Now, "dd-mm-yy h-mm-ss")
>>
>> Fname = "C:\Part of " & ThisWorkbook.Name _
>> & " " & strdate & ".csv"
>>
>> Application.ScreenUpdating = False
>> Sheets("CSV Data").Copy
>> Set wb = ActiveWorkbook
>> With wb
>> .SaveAs Fname, FileFormat:=xlCSV
>> .Close False
>> End With
>> Application.ScreenUpdating = True
>> End Sub
>>
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>>
>> "Husker87" <Husker87@discussions.microsoft.com> wrote in message news:20592323-BE14-4FC2-9DB1-D8795A3D1AC3@microsoft.com...
>> >I have a workbook with several worksheets. I want to record a macro that
>> > will save one tab, "CSV Data" as its own csv file to another location. Any
>> > ideas?
>>
>>
>>

[Husker87]

Worked GREAT. Thanks again. btw, why would you not want to save it to a
floppy but the c: drive first?

"Ron de Bruin" wrote:

> Hi
>
> First of all never save to a floppy this way.
> Always save to C:\ and copy manual to A
>
>
> Try this
>
> Sub Sheet_CSV_File_2()
> Dim wb As Workbook
> Dim strdate As String
>
> strdate = Format(Now, "dd-mm-yy h-mm-ss")
>
> Application.ScreenUpdating = False
> Sheets("CSV Data").Copy
> Set wb = ActiveWorkbook
> With wb
> .SaveAs "C:\" & wb.Sheets(1).Range("E1") & " " & _
> strdate & ".csv", FileFormat:=xlCSV
> .Close False
> End With
> Application.ScreenUpdating = True
> End Sub
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "Husker87" <Husker87@discussions.microsoft.com> wrote in message news:2FB79CE4-C691-475C-AA3D-38B1D2087201@microsoft.com...
> > Short follow up... btw this worked great. I changed the C: to A: so I could
> > save it to a disk. Here is my follow-up... can I change your code that
> > names the file to include the content of a cell on the CSV Data sheet? For
> > example the user has to enter a unique number into a cell "E1" on the CSV
> > Data worksheet. I would like the name of the CSV file to start with that
> > number in "E1" then followed by the date like you wrote it. Is that
> > possible?
> >
> > "Ron de Bruin" wrote:
> >
> >> Try this one to save the file in C:\
> >>
> >> Sub Sheet_CSV_File()
> >> Dim wb As Workbook
> >> Dim strdate As String
> >> Dim Fname As String
> >>
> >> strdate = Format(Now, "dd-mm-yy h-mm-ss")
> >>
> >> Fname = "C:\Part of " & ThisWorkbook.Name _
> >> & " " & strdate & ".csv"
> >>
> >> Application.ScreenUpdating = False
> >> Sheets("CSV Data").Copy
> >> Set wb = ActiveWorkbook
> >> With wb
> >> .SaveAs Fname, FileFormat:=xlCSV
> >> .Close False
> >> End With
> >> Application.ScreenUpdating = True
> >> End Sub
> >>
> >>
> >> --
> >> Regards Ron de Bruin
> >> http://www.rondebruin.nl
> >>
> >>
> >>
> >> "Husker87" <Husker87@discussions.microsoft.com> wrote in message news:20592323-BE14-4FC2-9DB1-D8795A3D1AC3@microsoft.com...
> >> >I have a workbook with several worksheets. I want to record a macro that
> >> > will save one tab, "CSV Data" as its own csv file to another location. Any
> >> > ideas?
> >>
> >>
> >>
>
>
>

[Ron de Bruin]

Good morning

> Worked GREAT. Thanks again. btw, why would you not want to save it to a
> floppy but the c: drive first?

The chance of file Corruption is big




--
Regards Ron de Bruin
http://www.rondebruin.nl



"Husker87" <Husker87@discussions.microsoft.com> wrote in message news:DEECE0A9-E015-4009-8E96-0DCE5C057271@microsoft.com...
> Worked GREAT. Thanks again. btw, why would you not want to save it to a
> floppy but the c: drive first?
>
> "Ron de Bruin" wrote:
>
>> Hi
>>
>> First of all never save to a floppy this way.
>> Always save to C:\ and copy manual to A
>>
>>
>> Try this
>>
>> Sub Sheet_CSV_File_2()
>> Dim wb As Workbook
>> Dim strdate As String
>>
>> strdate = Format(Now, "dd-mm-yy h-mm-ss")
>>
>> Application.ScreenUpdating = False
>> Sheets("CSV Data").Copy
>> Set wb = ActiveWorkbook
>> With wb
>> .SaveAs "C:\" & wb.Sheets(1).Range("E1") & " " & _
>> strdate & ".csv", FileFormat:=xlCSV
>> .Close False
>> End With
>> Application.ScreenUpdating = True
>> End Sub
>>
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>>
>> "Husker87" <Husker87@discussions.microsoft.com> wrote in message news:2FB79CE4-C691-475C-AA3D-38B1D2087201@microsoft.com...
>> > Short follow up... btw this worked great. I changed the C: to A: so I could
>> > save it to a disk. Here is my follow-up... can I change your code that
>> > names the file to include the content of a cell on the CSV Data sheet? For
>> > example the user has to enter a unique number into a cell "E1" on the CSV
>> > Data worksheet. I would like the name of the CSV file to start with that
>> > number in "E1" then followed by the date like you wrote it. Is that
>> > possible?
>> >
>> > "Ron de Bruin" wrote:
>> >
>> >> Try this one to save the file in C:\
>> >>
>> >> Sub Sheet_CSV_File()
>> >> Dim wb As Workbook
>> >> Dim strdate As String
>> >> Dim Fname As String
>> >>
>> >> strdate = Format(Now, "dd-mm-yy h-mm-ss")
>> >>
>> >> Fname = "C:\Part of " & ThisWorkbook.Name _
>> >> & " " & strdate & ".csv"
>> >>
>> >> Application.ScreenUpdating = False
>> >> Sheets("CSV Data").Copy
>> >> Set wb = ActiveWorkbook
>> >> With wb
>> >> .SaveAs Fname, FileFormat:=xlCSV
>> >> .Close False
>> >> End With
>> >> Application.ScreenUpdating = True
>> >> End Sub
>> >>
>> >>
>> >> --
>> >> Regards Ron de Bruin
>> >> http://www.rondebruin.nl
>> >>
>> >>
>> >>
>> >> "Husker87" <Husker87@discussions.microsoft.com> wrote in message news:20592323-BE14-4FC2-9DB1-D8795A3D1AC3@microsoft.com...
>> >> >I have a workbook with several worksheets. I want to record a macro that
>> >> > will save one tab, "CSV Data" as its own csv file to another location. Any
>> >> > ideas?
>> >>
>> >>
>> >>
>>
>>
>>

[Gord Dibben]

Husker

In order to record a macro one must go to Tools>Macro>Record New Macro.

And when done the macro usually must be edited somewhat.

Sub Macro2()
Dim w As Worksheet
Set w = Sheets("CSV Data")
Application.DisplayAlerts = False
w.Copy
ActiveWorkbook.SaveAs Filename:="E:\GordStuff\" & w.Name, _
FileFormat:=xlCSV
ActiveWorkbook.Close
Application.DisplayAlerts = True
End Sub


Gord Dibben Excel MVP

On Thu, 24 Mar 2005 12:55:05 -0800, "Husker87"
<Husker87@discussions.microsoft.com> wrote:

>I have a workbook with several worksheets. I want to record a macro that
>will save one tab, "CSV Data" as its own csv file to another location. Any
>ideas?