Copy Daily Data to the Next Excel Row with a VBA Macro
Question details
Copy selected cells from a calculator worksheet to specified columns in the next available row of a monthly worksheet.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user wants to append daily values from the Chemical Calculator worksheet to the September worksheet using VBA.
- Observed behavior
- The desired result is a new row of values in the specified destination columns, without copying source formatting.
Confirm that the worksheet names are Chemical Calculator and September, and check the source-to-destination mapping. The original list repeats B10; the example below assumes B20 should be copied to column R, so change it if the intended cell is B21 or another cell.
Use a VBA macro to append values to the September worksheet
Use this method in desktop Excel to transfer the mapped values without copying formatting.
The macro finds the last used row anywhere on the September sheet and writes to the row below it. It does not fill gaps above that row. On an empty sheet, it starts at row 1.
In desktop Excel, open the workbook, press Alt+F11, and choose Insert > Module in the VBA editor.
Paste this code into the module: Sub CopyData() Dim sourceSheet As Worksheet, targetSheet As Worksheet Dim lastCell As Range, nextRow As Long Dim sourceRows As Variant, targetCols As Variant Dim i As Long Set sourceSheet = ThisWorkbook.Worksheets("Chemical Calculator") Set targetSheet = ThisWorkbook.Worksheets("September") sourceRows = Array(4, 9, 10, 11, 12, 13, 15, 17, 18, 19, 20, 22, 23, 24, 26) targetCols = Array("B", "H", "I", "J", "K", "L", "M", "O", "P", "Q", "R", "AA", "AC", "AD", "AF") Set lastCell = targetSheet.Cells.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious) If lastCell Is Nothing Then nextRow = 1 Else nextRow = lastCell.Row + 1 End If For i = LBound(sourceRows) To UBound(sourceRows) targetSheet.Range(targetCols(i) & nextRow).Value = sourceSheet.Range("B" & sourceRows(i)).Value Next i End Sub
In the sourceRows and targetCols arrays, verify each matching position. For example, 20 and "R" copy Chemical Calculator!B20 to column R; replace 20 with 21 if B21 is intended.
Use File > Save As to save the workbook as an Excel Macro-Enabled Workbook (.xlsm). Return to Excel, open Developer > Macros or press Alt+F8, select CopyData, and click Run.
Open the September sheet and verify the values in columns B, H–M, O–R, AA, AC, AD, and AF. The destination cells retain their existing formatting.

Manage Daily Spreadsheet Data with WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface for working with daily records. It supports common Excel formats, making it easier to move existing files into WPS. VBA support can vary by edition and installation, so check macro compatibility before relying on the Excel macro.

Frequently Asked Questions
How do I change which source cell goes into a destination column?
In the VBA editor, change the relevant entry in sourceRows or targetCols. Entries at the same array position are paired: for example, 20 and "R" send B20 to column R.
Why does the macro place data below the visible table?
The Find command checks the entire September sheet for the last used row, including cells containing formulas. Look for data or formulas below the table and adjust the sheet or the row-finding logic as needed.
Does the macro copy formulas or formatting?
No. The .Value assignments transfer the current cell values, not the source formulas or formatting. Existing destination formatting remains in place.
Can I run this VBA macro in Excel for the web?
Excel for the web cannot run VBA macros. Open the workbook in desktop Excel and use Developer > Macros or Alt+F8 to run CopyData.
Why do I need to save the workbook as an XLSM file?
The .xlsm format can retain VBA code. Choose File > Save As and select Excel Macro-Enabled Workbook before closing the file.




