VBA & Macro Problems

Copy Daily Data to the Next Excel Row with a VBA Macro

Tauseeq MagsiTauseeq Magsi Nov 18, 2026 868 views

Question details

Copy selected cells from a calculator worksheet to specified columns in the next available row of a monthly worksheet.

Copy Daily Data to the Next Excel Row with a VBA Macro
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA editor

In desktop Excel, open the workbook, press Alt+F11, and choose Insert > Module in the VBA editor.

2
Insert the macro

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

3
Check the mapping

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.

4
Save and run the macro

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.

5
Check the new row

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.

Use a VBA macro to append values to the September worksheet
Rows are appended after all used cells: Data or formulas anywhere below the table can move the destination row farther down. Review the September sheet if the new data appears lower than expected.
Free Microsoft Office alternative

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.

Organize daily calculator results in spreadsheet rows and columnsOpen and edit Microsoft Excel formats such as XLSXUse a familiar interface to ease the move from Microsoft OfficeWork with a free, lightweight office suite
microsoft office alternative - wps office

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.