how to save a sheet in VBA

vba excel csv save xlsm
T.G. picture T.G. · Apr 23, 2017 · Viewed 14.8k times · Source

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

A.S.H picture A.S.H · Apr 23, 2017

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

Related questions

How to Insert Double or Single Quotes

I have a long list of names that I need to have quotes around (it can be double or single quotes) and I have about 8,000 of them. I have them in Excel without any quotes and I can copy all …

Read More
Save each sheet in a workbook to separate CSV files

How do I save each sheet in an Excel workbook to separate CSV files with a macro? I have an excel with multiple sheets and I was looking for a macro that will save each sheet to a separate CSV (…

Read More
Excel: macro to export worksheet as CSV file without leaving my current Excel sheet

There are a lot of questions here to create a macro to save a worksheet as a CSV file. All the answers use the SaveAs, like this one from SuperUser. They basically say to create a VBA function like this: …

Read More