save a single sheet in a workbook as a separate csv file

[bmcclure]

hi,

i want to save a single worksheet named TransferFile in a workbook as a separate CSV file. This is as far as i've gotten, but it saves every sheet as a new CSV file instead of just the one i want. not sure how to get it to do what i want.

Any help is greatly appreciated!
-B

Sub ExportFileUploadAsCSV()

Dim ws As Worksheet
Dim sFolderPath As String

sFolderPath = "B:\Operations\Brian\MACRO PROJECTS\Bond Upload\"

For Each ws In ThisWorkbook.Worksheets
If ws.Visible = xlSheetVisible Then
ws.Copy

ActiveWorkbook.SaveAs _
Filename:=sFolderPath & ws.Name, _
FileFormat:=xlCSV

ActiveWorkbook.Saved = True
ActiveWorkbook.Close False
End If

Next ws


End Sub

 

[Joe4]

That is because you are looping through every sheet in the workbook. Get rid of that loop, and try something like this:

Sub ExportFileUploadAsCSV()

    Dim ws As Worksheet
    Dim sFolderPath As String

    sFolderPath = "B:\Operations\Brian\MACRO PROJECTS\Bond Upload\"

    Set ws = Sheets("TransferFile")

    ws.Activate
    ActiveWorkbook.SaveAs _
        Filename:=sFolderPath & ws.Name, _
        FileFormat:=xlCSV

    ActiveWorkbook.Saved = True
    ActiveWorkbook.Close False

End Sub

 

[bmcclure]

this works thank you. one more question. is there a way to do it where it doesnt close the original file and open a blank workbook?

That is because you are looping through every sheet in the workbook. Get rid of that loop, and try something like this:

Sub ExportFileUploadAsCSV()

    Dim ws As Worksheet
    Dim sFolderPath As String

    sFolderPath = "B:\Operations\Brian\MACRO PROJECTS\Bond Upload\"

    Set ws = Sheets("TransferFile")

    ws.Activate
    ActiveWorkbook.SaveAs _
        Filename:=sFolderPath & ws.Name, _
        FileFormat:=xlCSV

    ActiveWorkbook.Saved = True
    ActiveWorkbook.Close False

End Sub

 

[Joe4]

Just get rid of the last two lines before the "End Sub"; these two in particular:

   ActiveWorkbook.Saved = True
    ActiveWorkbook.Close False

(the "ActiveWorkbook.Close..." is a dead giveaway!).

Note however, that the file left open will be the CSV file. So, if you make any changes and want to save it to the Excel file, you will have to do a SaveAs and select/enter the original file name and format.