Data Import & Export

Copy Data into Visible Cells of a Filtered Excel Table

Chanuka GeekiyanageChanuka Geekiyanage Nov 18, 2026 868 views

Question details

Update visible rows in a filtered Excel table with values matched from an unfiltered source table.

Copy Data into Visible Cells of a Filtered Excel Table
Product
Microsoft Excel
Device & OS
not provided
Scenario
Match source column B to destination column F, write source column F to destination column I, and mark updated rows in column J.
Observed behavior
Copying and pasting into the filtered destination can produce a copy-and-paste area size error.
Before you start

Confirm that source column B and destination column F contain the matching keys, and identify the destination table name under Table Design. Test the macro on a copy of the workbook; if the destination sheet is protected, obtain permission to edit columns I and J.

Solution 1Recommended

Use a VBA macro to update visible destination rows

Use this approach when the destination is an Excel table and you need to update filtered rows without pasting into hidden rows.

The macro checks the first matching source key and updates only destination rows that are not hidden. Matching is case-insensitive, and blank or error keys are skipped. It scans the source rows for each visible destination row, so large tables may take time.

1
Prepare the workbook

Save a test copy as an Excel Macro-Enabled Workbook (.xlsm). Name the source sheet Source and the destination sheet Destination, or change those names in the macro. On the destination sheet, select the table and set or check its name under Table Design > Table Name; the example uses DestinationTable.

2
Open the VBA editor

In desktop Excel, open Developer > Visual Basic, select the test workbook in the project pane, and choose Insert > Module.

3
Insert the macro

Paste the following code into the module. It assumes source data starts on row 2, with keys in column B and values in column F: Sub UpdateVisibleRows() Dim src As Worksheet, dst As Worksheet Dim tbl As ListObject, tableRow As Range Dim lastRow As Long, r As Long, destRow As Long Dim sourceKey As Variant, destKey As Variant Set src = ThisWorkbook.Worksheets("Source") Set dst = ThisWorkbook.Worksheets("Destination") Set tbl = dst.ListObjects("DestinationTable") If tbl.DataBodyRange Is Nothing Then Exit Sub lastRow = src.Cells(src.Rows.Count, "B").End(xlUp).Row For Each tableRow In tbl.DataBodyRange.Rows destRow = tableRow.Row If Not dst.Rows(destRow).Hidden Then destKey = dst.Cells(destRow, "F").Value2 If Not IsError(destKey) Then If Len(CStr(destKey)) > 0 Then For r = 2 To lastRow sourceKey = src.Cells(r, "B").Value2 If Not IsError(sourceKey) Then If Len(CStr(sourceKey)) > 0 Then If StrComp(CStr(sourceKey), CStr(destKey), vbTextCompare) = 0 Then dst.Cells(destRow, "I").Value2 = src.Cells(r, "F").Value2 dst.Cells(destRow, "J").Value = "Lowering price." Exit For End If End If End If Next r End If End If End If Next tableRow End Sub

4
Filter and run

Apply the desired filter on the destination table. In Excel, choose Developer > Macros, select UpdateVisibleRows, and click Run.

5
Check the results

Review columns I and J in the visible destination rows. Clear the filter using Data > Clear and verify that hidden rows were not changed.

Use a VBA macro to update visible destination rows
Check duplicates and protection: If a source key appears more than once, the macro uses the first match. It does not bypass worksheet protection; columns I and J must be editable.
Free Microsoft Office alternative

Work with Filtered Spreadsheets in WPS Office

WPS Office is a free, lightweight Microsoft Office alternative with a familiar spreadsheet interface and support for Excel formats, making it easy to move everyday spreadsheet work to WPS. It does not fix Excel's paste-area error.

Filter and review spreadsheet rows in WPS SpreadsheetsCompatible with Microsoft Excel XLS and XLSX filesFamiliar interface for a smoother move from Microsoft OfficeFree and lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel report a copy-and-paste area size error?

A filtered destination contains hidden rows, so the copied range and the selected destination areas may not have compatible sizes. Matching and updating rows with a macro avoids relying on that paste operation.

Will the macro change hidden destination rows?

No. It checks whether each destination table row is hidden and skips it, including rows hidden manually or by a filter.

What happens if a destination key has no source match?

The macro leaves columns I and J unchanged for that row. It also skips blank and error keys.

Can I run the macro on a protected worksheet?

Only if the relevant cells are editable under the worksheet's protection settings. Ask the workbook owner for permission or an authorized way to edit columns I and J before running it.