Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I copy a worksheet from a workbook as csv file
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? |
#2
|
|||
|
|||
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" wrote in message ... 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? |
#3
|
|||
|
|||
Husker
In order to record a macro one must go to ToolsMacroRecord 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" 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? |
#4
|
|||
|
|||
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" wrote in message ... 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? |
#5
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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? |
#6
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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? |
#7
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
cannot copy a worksheet within a workbook | Excel Discussion (Misc queries) | |||
I can't get a worksheet to copy onto the workbook | Excel Worksheet Functions | |||
Copy from worksheet to another x times | Excel Discussion (Misc queries) | |||
copy COLUMN from 1 worksheet to another (in a different workbook) | Excel Discussion (Misc queries) | |||
copy worksheet from closed workbook to active workbook using vba | Excel Worksheet Functions |