[Excel VBA] How to Export a Specific Cell Range as a CSV File

2025年12月30日

When managing data in Excel, you often need to save only a specific range as a CSV file. Manually selecting the range, copying it to a new workbook, and saving it as a CSV every time is a tedious process.

This article explains how to use a VBA macro to export a specified cell range to a CSV file with a single click.

Complete VBA Code

Copy and paste the following code into a standard module in your VBA editor.

Sub ExportRangeToCsv()

    ' Declare variables
    Dim targetRange As Range
    Dim newWb As Workbook
    
    ' --- Configuration Start ---
    
    ' Specify the cell range to export to CSV
    ' In this example, we specify B2:H12 on the active sheet
    Set targetRange = ThisWorkbook.ActiveSheet.Range("B2:H12")
    
    ' --- Configuration End ---
    
    ' Add a new workbook
    Set newWb = Workbooks.Add
    
    ' Copy the specified range to cell A1 of the first sheet in the new workbook
    targetRange.Copy newWb.Worksheets(1).Range("A1")
    
    ' Save the new workbook in CSV format
    ' The filename is "ExportData.csv", saved in the same folder as this Excel file
    newWb.SaveAs ThisWorkbook.Path & "\ExportData.csv", FileFormat:=xlCSV
    
    ' Close the temporary workbook without saving further changes
    newWb.Close SaveChanges:=False
    
    ' Display completion message
    MsgBox "CSV export of the specified range is complete."

End Sub

Detailed Code Explanation

1. Declaring Variables

The macro starts by declaring targetRange to hold the cell data and newWb to manage the temporary workbook used for the CSV conversion.

2. Specifying the Export Range

Set targetRange = ThisWorkbook.ActiveSheet.Range("B2:H12") defines the exact data area. You can change "B2:H12" to match your specific data requirements.

3. Creating a New Workbook and Copying Data

To create a clean CSV, we use Workbooks.Add to generate a temporary file. The targetRange.Copy command then transfers your data to the first sheet of this new workbook.

4. Saving in CSV Format

This is the core of the operation:

ThisWorkbook.Path: Automatically identifies the folder where your current Excel file is saved.
FileFormat:=xlCSV: This critical argument tells Excel to encode the file as a Comma Separated Values (CSV) format.

5. Cleanup

newWb.Close SaveChanges:=False closes the temporary workbook quietly in the background, ensuring your workspace remains clean and free of unnecessary pop-up messages.