Selection to CSV: Export Any Range to a Timestamped File in Two Clicks

TL;DR: Select a range of cells, run the macro, and a timestamped CSV file appears in the same folder as your workbook. No temp sheets to delete, no formulas accidentally leaked to the client, no Save As dialog. Pure export utility.

#Prerequisites & Setup

What you’ll need:

• Excel 2016+ (desktop)
• A workbook that has been saved at least once (the macro writes to the same folder)
• Any data you want to export — the macro works on any range in any workbook

Limitations:

• Only exports values, not formulas or formatting — the CSV is pure data
• The output is a standard CSV file (comma-separated, no Unicode BOM) — if your data contains commas in cell values, they will be quoted
• The workbook must be saved first (the macro needs a folder path)
• Does not export cell formatting, column widths, or multiple sheets at once — this is a range-to-CSV export, not a full workbook conversion

#The Macro

Option Explicit

Sub SelectionToCSV()
    ' ── Selection to CSV ───────────────────────────────
    ' Exports the selected range to a timestamped CSV
    ' file in the same folder as the workbook. Values
    ' only — formulas, formatting, and cell styles are
    ' not included. Two clicks: select range, run macro.
    '
    ' Output filename: WorkbookName_yyyy-mm-dd_hhmm.csv
    ' ────────────────────────────────────────────────────

    ' ── State management ───────────────────────────────
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    ' ── Variables ──────────────────────────────────────
    Dim rng As Range
    Dim wsTemp As Worksheet
    Dim fname As String
    Dim ts As String
    Dim fullPath As String

    ' ── Error handling ─────────────────────────────────
    On Error GoTo CleanUp

    ' ── Step 1: Get the range to export ────────────────
    On Error Resume Next
    Set rng = Application.InputBox( _
        "Select the range to export as CSV:", _
        "Export Range to CSV", Type:=8)
    On Error GoTo CleanUp

    If rng Is Nothing Then
        MsgBox "Export cancelled.", vbInformation, "Cancelled"
        GoTo CleanUp
    End If

    ' ── Step 2: Validate workbook is saved ─────────────
    If Len(ThisWorkbook.Path) = 0 Then
        MsgBox "Save the workbook first. The macro needs a " & _
               "folder path to write the CSV file.", _
               vbExclamation, "Workbook Not Saved"
        GoTo CleanUp
    End If

    ' ── Step 3: Copy values to a temp sheet ────────────
    Set wsTemp = ThisWorkbook.Sheets.Add
    rng.Copy
    wsTemp.Range("A1").PasteSpecial xlPasteValues
    wsTemp.Range("A1").PasteSpecial xlPasteFormats
    Application.CutCopyMode = False

    ' ── Step 4: Generate filename with timestamp ───────
    ts = Format(Now, "yyyy-mm-dd_hhmm")
    fname = Left(ThisWorkbook.Name, _
                 InStrRev(ThisWorkbook.Name, ".") - 1) & _
            "_" & ts & ".csv"
    fullPath = ThisWorkbook.Path & "\" & fname

    ' ── Step 5: Save temp sheet as CSV, then delete ────
    wsTemp.SaveAs fullPath, xlCSV
    wsTemp.Delete

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

    ' ── Step 6: Report ─────────────────────────────────
    MsgBox "Exported " & rng.Rows.Count & " row(s) × " & _
           rng.Columns.Count & " col(s)." & vbCrLf & vbCrLf & _
           "File: " & fname, vbInformation, "Export Complete"

    Exit Sub

CleanUp:
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

    If Err.Number <> 0 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, _
               vbCritical, "Macro Error"
    End If
End Sub

#How It Works

#The temp-sheet trick — clean values, no artifacts

The macro creates a hidden temporary worksheet, pastes the values from your selected range into it, saves the temp sheet as a CSV, then deletes it. You never see the temp sheet. The original workbook is untouched — no formulas overwritten, no sheets renamed.

Set wsTemp = ThisWorkbook.Sheets.Add
rng.Copy
wsTemp.Range("A1").PasteSpecial xlPasteValues

Using xlPasteValues ensures the CSV file contains the displayed numbers, not the underlying formulas. If cell G14 is =B14*C14 showing $1,247,000, the CSV gets $1,247,000 — not =B14*C14.

#Timestamped filename — never overwrite a previous export

ts = Format(Now, "yyyy-mm-dd_hhmm")
fname = Left(ThisWorkbook.Name, _
             InStrRev(ThisWorkbook.Name, ".") - 1) & _
        "_" & ts & ".csv"

Every export gets a unique filename based on the current date and time: Henderson-TB_2027-08-01_1435.csv. If you export three times in the same minute (e.g., different ranges from the same workbook), the last one will overwrite the previous. For production use, add seconds to the timestamp with Format(Now, "yyyy-mm-dd_hhmmss").

The Left(..., InStrRev(...)) pattern strips the file extension from the workbook name, so Henderson-TB.xlsm becomes Henderson-TB and the timestamp is appended cleanly.

#Why the workbook must be saved first

The macro writes to ThisWorkbook.Path & "\" & fname. If the workbook has never been saved, ThisWorkbook.Path is an empty string and the SaveAs call will fail with a cryptic error. The early-exit guard gives the user a clear message:

If Len(ThisWorkbook.Path) = 0 Then
    MsgBox "Save the workbook first..."
    GoTo CleanUp
End If

#What DisplayAlerts = False does for you

Excel normally prompts with “Do you want to keep using this format?” when saving a workbook as CSV. Setting Application.DisplayAlerts = False suppresses that dialog. The macro also suppresses the “Are you sure you want to delete this sheet?” prompt when removing the temp sheet.

Both flags are restored in the CleanUp label, even if the macro crashes mid-execution. A suppressed alert that stays suppressed is a recipe for losing data in a later operation.

#Why InputBox(Type:=8) instead of a typed range address

Set rng = Application.InputBox( _
    "Select the range to export as CSV:", _
    "Export Range to CSV", Type:=8)

Type:=8 tells Excel to accept a range object — the user selects cells with their mouse. This eliminates typos (“D8:D15o” instead of “D8:D150”), handles sheet references automatically, and works regardless of which sheet the user is viewing. It’s the same gesture as selecting a range for a chart or pivot table — zero learning curve.

#Adapt It