Copy Data into Visible Cells of a Filtered Excel Table
Question details
Update visible rows in a filtered Excel table with values matched from an unfiltered source 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.
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.
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.
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.
In desktop Excel, open Developer > Visual Basic, select the test workbook in the project pane, and choose Insert > Module.
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
Apply the desired filter on the destination table. In Excel, choose Developer > Macros, select UpdateVisibleRows, and click Run.
Review columns I and J in the visible destination rows. Clear the filter using Data > Clear and verify that hidden rows were not changed.

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.

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.




