how to save a sheet in VBA

Question

I'm building a form that when you fill it up, it should generate 2 files as output.

In order to do that I have 2 sheets in the background that I fill up depending on what the user entered a button to save them (as csv).

This is the code for saving the sheet-

Worksheets("worksheetname").SaveAs Filename:="C:\path" & name & ".csv", FileFormat:=xlCSV, CreateBackup:=False

The problem is that after the user presses the button, the form that he is filling is now 'saved as csv', and not xlsm like before.

Is there a way to avoid that from happening? to generate the output without affecting the form?

Thanks!

Answer 1

To keep the original workbook intact, you need to fork a copy of the worksheet before saving it as CSV. Try this:

Application.DisplayAlerts = False
Worksheets("worksheetname").copy '  Forks a copy of the sheet in a new, active WB
With ActiveWorkbook
    .SaveAs Filename:="C:\Path\" & name & ".csv", FileFormat:=xlCSV, CreateBackup:=False
    .Close False
End With
Application.DisplayAlerts = True