VBA & Macro Problems

Add Excel Table Rows with Values Only Using VBA

Aamir Naveed AkramAamir Naveed Akram Nov 18, 2026 869 views

Question details

Add rows to an Excel table and transfer values from a source table using VBA.

Add Excel Table Rows with Values Only 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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA editor

In Excel, open the Developer tab and select Visual Basic. In the editor, select Insert > Module.

2
Add the macro

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

3
Run and check the results

In Excel, select Developer > Macros, choose AddTableRowsValuesOnly, and click Run. Check the destination table for the added rows and values.

Assign Source Values to New Excel Table Rows with VBA
Add just one row: To transfer only the first source row, use destinationTable.ListRows.Add.Range.Value = sourceTable.ListRows(1).Range.Value after defining both tables. The source table must contain at least one row.
Free Microsoft Office alternative

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.

Paste spreadsheet values without copying source formulasOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar interface for an easier move from ExcelWork with a lightweight, free Office alternative
microsoft office alternative - wps office

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.