MacroProblem-Need Code to Save Worksheet as CSV to Desktop

[cgeorge4]

Hello,

I need a code that will save the active worksheet within my workbook as a CSV file - to my desktop. I'm sorry that I don't currently have a code to show you. I have been everywhere and have not been able to find exactly what I'm needing. I have found some codes but not any with a message box.

I'm using Excel 2010.

I have a workbook that has several worksheets - but I only want to save the worksheet that is currently active - as a CSV file - not the whole workbook.

Would it be possible to be prompted by a message box that will ask you how you would like to name the CSV file before that code runs?

In case you need to know, and the number of characters is important - the name of the file would be many characters long. Something like XACCTFEEDIN.8182011.8192011new. If it doesn't matter than that is great!


Thank you so so much,
Juicy,

 

[mikerickson]

Sub SaveAsCSV()
    Application.Dialogs(xlDialogSaveAs).Show , 6
End Sub

 

[cgeorge4]

Hi,

Thank you for responding so quickly.

I tried your code several times and it worked to a certain degree.

After I ran your code, I went to my desktop and I did find the file - but when I opened it, it was saved with all the sheets that were in my workbook.

....It did have an additional tab with the name I saved file name under - which is great....but it is really necessary that only the active sheet be saved as a CSV file. When I'm ready to upload this file - our accounting system won't upload a file with more than one sheet.

Almost there!

Thanks,
Juicy,

 

[mikerickson]

Sub test()
    SaveSheetAsCSV
End Sub

Sub SaveSheetAsCSV(Optional SheetToSave As Worksheet)
    If SheetToSave Is Nothing Then Set SheetToSave = ActiveSheet
    
    SheetToSave.Copy
    
    If Not Application.Dialogs(xlDialogSaveAs).Show(, 6) Then
        ActiveWorkbook.Close SaveChanges:=False
    End If
End Sub

 

[cgeorge4]

You are Brilliant!!!!!!.

I ran your code several times - on workbooks with varying number of worksheets, and it only copied the active worksheet to my desktop.


It ran perfectly each time.....thank you sooooooo much,

Juicy, WOW!