Use an Excel VBA Macro to Copy Values from XLSM to CSV
Question details
Copy values from an XLSM workbook to a CSV file using a reusable macro, without creating a separate button for every date.

- 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.
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.
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.
In the XLSM workbook, open the Visual Basic editor from Developer > Visual Basic, then choose Insert > Module.
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
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.
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.

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.

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.




