VBA & Macro Problems

Use an Excel VBA Macro to Copy Values from XLSM to CSV

Tauseeq MagsiTauseeq Magsi Nov 22, 2026 868 views

Question details

Copy values from an XLSM workbook to a CSV file using a reusable macro, without creating a separate button for every date.

Copy XLSM Values to a CSV File with an Excel VBA Macro
Product
Microsoft Excel
Device & OS
not provided
Scenario
The user needs to transfer a source range to an existing CSV and change the source or destination for different dates.
Observed behavior
A single macro can use configurable ranges and a file path to write values to the appropriate CSV cells.
Before you start

Identify the source worksheet and range, the existing CSV file path, and the first destination cell. Open the XLSM workbook in a desktop version of Excel that supports VBA and enable macros if prompted.

Solution 1Recommended

Use one configurable VBA macro to update the CSV

Use this macro when you want to change the source range, CSV path, or destination cell without creating a new macro for each date.

The macro copies a rectangular range of values into the first worksheet of an existing CSV file. It uses ThisWorkbook as the source, so the code must be stored in the XLSM workbook containing the data.

1
Open the VBA editor

In the XLSM workbook, open the Visual Basic editor from Developer > Visual Basic, then choose Insert > Module.

2
Add the macro

Paste this code into the module: Sub CopyValuesToCSV() Dim sourceSheet As Worksheet Dim sourceRange As Range Dim targetBook As Workbook Dim targetCell As Range Dim csvPath As String Dim previousAlerts As Boolean previousAlerts = Application.DisplayAlerts On Error GoTo HandleError csvPath = "C:\YourFolder\YourFile.csv" Set sourceSheet = ThisWorkbook.Worksheets("P7") Set sourceRange = sourceSheet.Range("B3:B20") Set targetBook = Workbooks.Open(csvPath) Set targetCell = targetBook.Worksheets(1).Range("F2") targetCell.Resize(sourceRange.Rows.Count, sourceRange.Columns.Count).Value2 = sourceRange.Value2 Application.DisplayAlerts = False targetBook.Save targetBook.Close SaveChanges:=False Application.DisplayAlerts = previousAlerts MsgBox "Values copied to the CSV file." Exit Sub HandleError: Application.DisplayAlerts = previousAlerts If Not targetBook Is Nothing Then targetBook.Close SaveChanges:=False MsgBox "Could not update the CSV: " & Err.Description End Sub

3
Set your source and destination

In the code, replace the example CSV path, worksheet name, source range B3:B20, and destination cell F2 with your own. The destination range expands to match the source range.

4
Save and run

Save the source workbook as an XLSM file. In Excel, choose Developer > Macros, select CopyValuesToCSV, and click Run. If you use a button, assign this same macro to it rather than creating a button for each date.

Use one configurable VBA macro to update the CSV
Check the CSV output: The macro overwrites the destination cells and saves the CSV. CSV stores text values rather than Excel formatting or formulas; Excel may also interpret dates and leading zeros when opening a CSV.
Free Microsoft Office alternative

Work with CSV files in WPS Spreadsheets

WPS Office is a free, lightweight alternative for opening, editing, and saving spreadsheets. Its familiar interface and Microsoft Office format compatibility make it easy to move everyday spreadsheet work from Excel; it does not fix or guarantee support for this Excel VBA macro.

Open and edit CSV data in a spreadsheet interfaceCompatible with Microsoft Excel formats, including XLSXFree and lightweight for everyday office tasksFamiliar controls help make migration from Microsoft Office easier
microsoft office alternative - wps office

Frequently Asked Questions

How do I copy to a different column in the CSV?

Change the destination cell in targetBook.Worksheets(1).Range("F2") to the first cell of the desired column, such as H2.

Can I use the same macro for a different date?

Yes. Open Developer > Visual Basic and update csvPath, the source range, or the destination cell for the next date. You can then run the same macro from Developer > Macros.

Does the macro copy formulas and formatting?

No. The Value2 assignment copies values, not formulas or formatting. CSV files do not preserve Excel formatting.

Why can't Excel find the CSV file?

Check that csvPath contains the full path and filename of an existing CSV file and that you have permission to open it. The example path must be replaced before running the macro.