Excel VBA Export Range to CSV, Text, or Another Sheet

VBA & macros · Updated November 21, 2026

To export an Excel range with VBA, copy it to a temporary workbook and save that workbook as a CSV file; for Microsoft 365 on Windows, the macro below exports a chosen range as UTF-8 CSV. Change the worksheet name and range to match your data, then run the macro from the workbook containing the source range.

What “export a range” can mean

Exporting a range means taking a defined block of cells out of a worksheet and writing it somewhere else. The destination might be a CSV file for another application, a separate Excel workbook, a text file, or a PDF. The right VBA method depends on the destination and on whether you need formulas, formatting, or just the displayed results.

A CSV file is a plain-text table, not an Excel workbook. It can hold rows and columns of values, but it does not retain cell colors, formulas as formulas, multiple worksheets, charts, or column widths. An XLSX workbook can preserve more structure. A PDF is useful for a fixed presentation, but it is not a convenient format for further data analysis.

The sample below assumes the data is on a worksheet named Data in cells A1:D100. It copies that block to a temporary one-sheet workbook and saves it as UTF-8 CSV. Copying first lets Excel handle CSV quoting and separators instead of assembling every text row manually.

VBA macro to export a range as UTF-8 CSV

In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste in this procedure. On a Mac, the Visual Basic Editor shortcut differs by keyboard and Excel settings; use the Developer tab or Excel’s Tools > Macro > Visual Basic Editor command if the shortcut is unavailable. Save the source workbook as an Excel Macro-Enabled Workbook (.xlsm) before using VBA.

Option Explicit

Sub ExportRangeToCSV()
    Dim sourceRange As Range
    Dim tempBook As Workbook
    Dim filePath As Variant
    Dim oldAlerts As Boolean

    oldAlerts = Application.DisplayAlerts
    On Error GoTo CleanFail

    ' Set the worksheet and cells to export.
    Set sourceRange = ThisWorkbook.Worksheets("Data").Range("A1:D100")

    ' Ask where to save the CSV file.
    filePath = Application.GetSaveAsFilename( _
        InitialFileName:="export.csv", _
        FileFilter:="CSV UTF-8 (*.csv), *.csv")
    If filePath = False Then Exit Sub

    ' Copy the range into a temporary one-sheet workbook.
    Set tempBook = Workbooks.Add(xlWBATWorksheet)
    sourceRange.Copy Destination:=tempBook.Worksheets(1).Range("A1")

    ' Save as UTF-8 CSV, then discard the temporary workbook.
    Application.DisplayAlerts = False
    tempBook.SaveAs Filename:=CStr(filePath), FileFormat:=xlCSVUTF8
    tempBook.Close SaveChanges:=False
    Application.DisplayAlerts = oldAlerts
    Exit Sub

CleanFail:
    On Error Resume Next
    If Not tempBook Is Nothing Then tempBook.Close SaveChanges:=False
    Application.DisplayAlerts = oldAlerts
    On Error GoTo 0
    MsgBox "The range could not be exported. Check the range and file location.", vbExclamation
End Sub

Before running any macro, save a copy of the workbook. VBA actions generally cannot be reversed with Ctrl+Z. To run this procedure, return to Excel, press Alt+F8, select ExportRangeToCSV, and choose Run. On Mac, use the Macro command in Excel if the Windows shortcut is not applicable. The file picker asks for the destination; canceling it exits without making a CSV.

Choose the range and destination carefully

Replace Data with the exact worksheet tab name, and change A1:D100 to the cells you intend to export. The worksheet name must match, including spaces. For a sheet called Monthly Report, the reference would be ThisWorkbook.Worksheets("Monthly Report"). A fixed address is predictable, but it will not automatically expand when new records are added.

For a range that changes size, you can set sourceRange to a named Excel Table’s data area or determine the last populated row before assigning the range. For example, if column A is always filled for every record, a macro can find its last used row and build a range from A1 to the last row. Choose the anchor column carefully: a blank cell in that column can make the detected range end too soon. If the table has totals or notes below it, a Table-based range is often safer than guessing by the last occupied cell.

  • Include the header row if the receiving program needs field names.
  • Exclude explanatory notes, blank spacer rows, and totals unless they belong in the exported data.
  • Check that the selected area is one continuous rectangle. A CSV represents rows and columns, not separated selections.
  • Decide whether hidden rows should be included. Copying a normal range can include cells hidden by filtering or manually hiding rows.

What the CSV contains—and what it does not

The macro copies cells into a temporary workbook and asks Excel to save that workbook as CSV UTF-8. In a CSV, formulas become their calculated results; the formula itself is not preserved. Formatting is also not carried over as workbook formatting. Excel writes the cell contents in a text-table format, applying its CSV rules for delimiters and quoting. UTF-8 is useful when names or descriptions contain characters outside basic English letters.

Dates, decimal separators, and number display can require attention when another system imports the file. CSV does not store a date as an Excel date type or retain the number format that tells Excel how to display a serial value. A receiving application may interpret dates or decimal marks according to its own regional settings. If the receiving system expects a specific date pattern, prepare a dedicated export range with values formatted as the required text, and test a sample import before relying on a recurring process.

Leading zeros are another common trap. A value such as an account code may be stored as the number 123 with a display format that shows 00123. CSV generally stores the underlying value rather than the Excel display format, so the zeros can disappear. Store identifiers as text or create an export column that explicitly contains the intended text. Do not assume that opening a CSV in Excel will preserve import interpretation; Excel may automatically convert text that resembles dates or numbers.

Exporting to another Excel workbook instead

If the recipient needs formulas, formatting, or a separate worksheet, use an XLSX workbook rather than CSV. A basic VBA approach is to create a new workbook and copy the source range into its first sheet. The following version pastes values and number formats, not formulas, so the exported values do not depend on references back to the original workbook:

Sub CopyRangeToNewWorkbook()
    Dim sourceRange As Range
    Dim outputBook As Workbook

    ' Set the source cells.
    Set sourceRange = ThisWorkbook.Worksheets("Data").Range("A1:D100")

    ' Create a workbook and paste values with number formats.
    Set outputBook = Workbooks.Add(xlWBATWorksheet)
    sourceRange.Copy
    outputBook.Worksheets(1).Range("A1").PasteSpecial _
        Paste:=xlPasteValuesAndNumberFormats
    Application.CutCopyMode = False

    ' Use Save As in Excel to choose the XLSX name and location.
End Sub

The new workbook remains open so you can inspect it and choose Save As. If you need formulas or full visual formatting, use a different paste choice or copy the range normally, then check formulas for links back to the source workbook. Relative and external references can behave differently after copying, so verify the output before sharing it.

Other export formats and alternatives

For a tab-delimited text file, Excel’s text file formats may be suitable, but the correct separator and encoding depend on the receiving application and regional settings. For a PDF, use Excel’s export or save-as options when the goal is a printable page; define the print area and page layout first. VBA can automate those formats too, but they use different file-format settings and do not behave like a data export.

For a one-time task, selecting the range and copying it into a new workbook may be quicker and easier to inspect than writing a macro. Excel’s Save As command can also save a workbook as CSV, but it saves the active sheet rather than an arbitrary selected block. A temporary workbook is useful when you need a repeatable export of only a specific range. For repeated, governed data transformations, Power Query may be a better fit than VBA, depending on the destination and the steps required.

Common export errors and troubleshooting

  • “Subscript out of range”: Check that the worksheet tab is named exactly as the code says and that the macro is running from the workbook containing that sheet. ThisWorkbook means the workbook where the VBA code resides.
  • Wrong cells or missing records: Confirm the address and whether the range should expand. A fixed A1:D100 reference will not include row 101.
  • The CSV has unexpected characters: Confirm the UTF-8 format is supported by the Excel version and that the receiving application imports UTF-8. Check the file in a text editor or through the receiving application’s import process.
  • The file is overwritten without a warning: The sample turns off Excel’s display alerts while saving, which can suppress the overwrite prompt. Choose a new filename when prompted, or add explicit overwrite confirmation if your workflow requires it.
  • Export fails due to permissions: Try a folder where you have write access and check that the file is not locked by another application. Network locations and synchronized folders can also have access or timing issues.
  • Unexpected formulas or values: CSV contains results rather than reusable formulas. Recalculate the workbook if necessary, and check formula results before export.

Macro security and platform notes

VBA macros run in desktop Excel, not Excel for the web. The web app can open and edit many macro-enabled workbooks, but it cannot run the VBA procedure. In Microsoft 365 for Windows, enable macros only for files and locations you trust; do not lower security settings just to run an unknown workbook. A downloaded workbook may open in Protected View or have macros blocked by organizational policy.

Excel for Mac supports VBA, but file paths, dialogs, and some save-format behaviors can differ from Windows. This example uses Excel’s file picker rather than a hard-coded Windows path, but check the resulting file and encoding on the Mac version you use. If a macro is shared across operating systems, avoid Windows-only file-system assumptions and test it on each platform. Always retain the source workbook and review the exported file before sending it to another person or system.

Frequently Asked Questions

Can VBA export only the selected cells?
Yes. Assign Selection to a Range variable and copy that range, but validate the selection first so the macro does not export an unintended cell or multiple areas. A fixed, named range is generally safer for a repeatable task.

Does exporting a range to CSV keep formulas?
No. CSV is a plain-text data format, so Excel writes calculated results rather than preserving formulas. Use an Excel workbook format if the recipient must keep formulas, formatting, or multiple worksheets.

Can I export a range without the headers?
Yes. Set the source address to begin on the first data row instead of the header row, or adjust the range before copying. Keep headers when the receiving application uses them to identify columns.

Why did my leading zeros disappear?
They may have existed only as a number format, not as part of the cell’s stored value. Save identifiers as text or prepare a text-valued export column before creating the CSV, then verify the output by importing it as text where appropriate.

Can Excel for the web run an export macro?
No. Excel for the web does not run VBA macros. Open the workbook in the desktop version of Excel to run the procedure, or use a web-compatible alternative such as a manual download or an appropriate automation service.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.