Add Excel Table Rows with Values Only Using VBA
Question details
Add rows to an Excel table and transfer values from a source table using VBA.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user wants to populate new destination table rows from source table rows without copying formulas or source formatting.
- Observed behavior
- The destination rows should receive the source cell values without using the clipboard.
Check the worksheet and table names in Table Design, and confirm that the source and destination tables have the same number of columns in the same order. Use desktop Excel with macros enabled for a trusted workbook.
Assign Source Values to New Excel Table Rows with VBA
Use direct Value assignment to add each source row to a destination table without copying cells through the clipboard.
The assignment transfers cell values, including the current results of formulas, rather than the formulas themselves. The destination table can still apply its own formatting or calculated-column behavior.
In Excel, open the Developer tab and select Visual Basic. In the editor, select Insert > Module.
Paste this code into the module, replacing the worksheet and table names with your own: Sub AddTableRowsValuesOnly() Dim sourceTable As ListObject Dim destinationTable As ListObject Dim i As Long Set sourceTable = ThisWorkbook.Worksheets("Source").ListObjects("MySourceTable") Set destinationTable = ThisWorkbook.Worksheets("Destination").ListObjects("MyDestinationTable") If sourceTable.ListColumns.Count <> destinationTable.ListColumns.Count Then MsgBox "The tables must have the same number of columns." Exit Sub End If For i = 1 To sourceTable.ListRows.Count destinationTable.ListRows.Add.Range.Value = sourceTable.ListRows(i).Range.Value Next i End Sub
In Excel, select Developer > Macros, choose AddTableRowsValuesOnly, and click Run. Check the destination table for the added rows and values.

Work with Excel Table Values in WPS Spreadsheets
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel files and use values-only pasting for manual transfers; Excel VBA macro compatibility can vary.

Frequently Asked Questions
Does direct Value assignment copy formulas?
No. Assigning the source range's Value to the destination range transfers the current cell values, including formula results, rather than the formulas.
Why does the destination row still have formatting?
The assignment does not copy source formatting, but the destination table can apply its own table style to newly added rows.
What happens if the source table has no data rows?
sourceTable.ListRows.Count is zero, so the For loop does not add any rows. A direct reference to ListRows(1), however, requires at least one source row.
Can I paste values without using VBA?
Yes. Copy the source cells, select the destination cells, and choose Paste Special > Values. Add enough destination rows first and confirm they are included in the table.




