0

I have a macro that saves the current workbook as CSV MSDOS format.

Here's my code:

Sub Save_CSV()
Dim Location, FileName As String
            Location = "C:\Users\myawesomename\OneDrive\Desktop\GM MRP\"
            FileName = Left(ActiveWorkbook.name, Len(ActiveWorkbook.name) - 5)
                ActiveWorkbook.SaveAs FileName:= _
                    Location & FileName & ".csv", FileFormat:= _
                    xlCSVMSDOS, CreateBackup:=False
                ActiveWorkbook.Save
End Sub

After I use this macro, I'm no longer working on the xlsv. Rather, I'm working on the CSV version with all the sheets still present. If I close the workbook without saving it, I can then open the CSV file and only the first sheet is present. It's fine that only the first sheet is present but I want it to save a separate CSV file (with the first sheet only present) while continuing to work on the XLSX file without opening the CSV at all. I'm not trying to save each sheet as a separate file.

I tried several things including changing "Activeworkbook.SaveAs" to "Activeworkbook.savecopyas" but I couldn't achieve the desired result.

Thank you,

Sabatino Ognibene
  • 142
  • 13
  • 1
    So you are supplying it with one filename but expecting it to output multiple files? It doesnt work like that - Excel has no option for doing what you want. You would have to loop through all of the sheets and save each one individually because CSV files do not support saving multiple sheets. – braX Nov 27 '19 at 05:22
  • 1
    Possible duplicate of [Save each sheet in a workbook to separate CSV files](https://stackoverflow.com/questions/59075/save-each-sheet-in-a-workbook-to-separate-csv-files) – braX Nov 27 '19 at 05:24
  • No I'm not trying to save each worksheet. The one worksheet it saves is fine. I just need it to save as a copy and let me keep working on the XLSX file. SaveAsCopy does not work. Interesting that you linked the save each sheet thing because I already read that before posting. – Sabatino Ognibene Nov 27 '19 at 06:01
  • 1
    OK, then how about this one? https://stackoverflow.com/questions/34155718/vba-saving-single-sheet-as-csv-not-whole-workbook - it makes a copy of your active sheet, and then saves that as the CSV, leaving your multisheet workbook in tact. – braX Nov 27 '19 at 06:05
  • braX that's very close. The code is truncated in that link however. IE: lastcol and lastrow are not DIMed or Set. I will have to look at it tomorrow and see if I can piece it back together. If I'm able to figure it out based on that link, I will responding saying as much. – Sabatino Ognibene Nov 27 '19 at 06:38
  • Sounds good - make sure to look at the accepted answer, and not the code in the question itself (which is truncated as you say) – braX Nov 27 '19 at 06:39

0 Answers0