score:4

Accepted answer

Use ws.Copy with no args beforehand then save the new workbook

e.g. adapt your code to be like:

    Dim ws              As Excel.Worksheet
    Dim strSaveName     As String

    strSaveName = Application.GetSaveAsFilename( _
        fileFilter:="Text Files (*.csv), *.csv")

    Set ws = Worksheets("Export")

    'Copy the ws to a new workbook
    ws.Copy
    'With the new wb:
    With Workbooks(Workbooks.Count)
        'Save and close the new workbook
        .SaveAs Filename:=strSaveName, FileFormat:=xlCSV
        .Close False
    End With

p.s. I assume you have code to handle clicking Cancel on GetSaveAsFilename and just removed it for clarity in the question ;)


Related Query

More Query from same tag