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

[Guest]

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

 

[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

 

[Guest]

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]

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

 

[Guest]

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

 

[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