How To Save An Individual Sheet As A CSV Using VBA (Includes Easy To Install Code) -

In some cases it may be necessary to save an individual worksheet as a CSV file within a directory. In this article we will show you how to quickly and easily install code that will allow you to select a sheet from a drop down list and convert it to a new CSV at the push of a button.

The code is designed to name the file based upon the name of the sheet and the date and time in which the sheet is converted to a CSV which will ensure that it produces the unique filename.

The instructions provided in this article are designed to be suitable for somebody that does not know anything about VBA so don’t worry if you haven’t used VBA before.

Setting Up Your Excel File To Save CSV’s

To setup your CSV file there are a couple of things that need to be done.

Step 1: Create a new Excel spreadsheet and save it as an Macro-Enabled Workbook. Please note that this is not the default setting for excel.

Step 2: Go into the Visual Basic Editor by pressing ALT + F11

Step 3: Create a new module By selecting insert and then module. the module will appear on the left-hand side of the screen and is circled in red.

Step 4: Select the module that has just been created and copy the code below into the module.

Sub SavingSheet_As_A_CSV()

Dim ws As Worksheet
Dim sFileName As String
Dim WB As Workbook
Dim Sheetname

Application.DisplayAlerts = False

'gets the Names of each sheet and places it into the combobox
For Each ws In Worksheets
     
UserForm1.ComboBox1.AddItem ws.Name

Next ws
'shows userform to get user input
UserForm1.Show

Sheetname = UserForm1.ComboBox1.Value

'create file name with sheet name date and time
sFileName = Sheetname & " " & Day(Date) & "-" & Month(Date) & "-" & Year(Date) & " " & Hour(Time) & "h-" & Minute(Time) & "m" & ".csv"

'Copy the contents of required sheet ready to paste into the new CSV
Worksheets(Sheetname).Range("A:Z").Copy 'Define your own range

'Open a new XLS workbook, save it as the file name
Set WB = Workbooks.Add
    
    'pastes in data into sheet and closes file
    With WB
        .Title = "MyTitle"
        .Subject = "MySubject"
        .Sheets(1).Select
        ActiveSheet.Paste
        .SaveAs "C:\Users\server\OneDrive\Documents\test\" & sFileName, xlCSV
        .Close
    End With

Application.DisplayAlerts = True

End Sub

Step 5: The location where the files will be saved needs to be modified within the code to suit the location where you want to save the files. This line of code is highlighted in bold above.

If you are not 100% sure what the file path is for your location the easiest way to find it is to right click on a file that is already stored in the location where you want the files stored and select properties.

The file path can be copied from the information provided.

IMPORTANT: The file path provided must have an additional backlash added at the end. For the example below the path provide is C:\Users\server\OneDrive\Documents\test. The path that needs to be added to the code is C:\Users\server\OneDrive\Documents\test\.

Step 6: For the code to work as intended a userform needs to be added to the file to allow the selection to be made. To add a userform select insert and then userform.