Copy/Paste visible

Location on ribbon: Tab DVD Addin → Group Cells and Rows → Menu Merge cells

Command ID: btnCopyPasteVisible

License required

This command needs a valid DVDAddin account (Standard or PRO). It is fully available during the trial period.

What it does

Excel refuses to paste into a filtered range properly — the data spills into the hidden rows and wrecks the table. This command copies from a source range into a target range where both sides count visible rows only, writing in left→right, top→bottom order and always into the top-left cell of any merged area.

Two matching modes are available: sequential (visible row order) or condition column matching, which behaves like a VLOOKUP but writes the value — or a reference formula — straight into the target table.

How to use

1. Filter or hide rows on both the source and target sheets so only the relevant rows show.
2. Click DVD Addin → Cells and Rows → Merge cells → Copy/Paste visible. The Copy Paste Visible window opens.
3. Paste mode: choose Paste values (static values) or Paste as formula reference (=source address), which writes an absolute reference to the source cell so the target updates when the source changes.
4. Tick Use condition column (first column = key, last column = value) for key matching: the command finds the target row whose column 1 equals the source column 1 and pastes the matching last-column value.
5. Click Continue.
6. The prompt "Select range to copy" appears — drag over the source range and click OK. Then "Select range to paste" — drag over the target range and click OK.
7. The result is written immediately; hold ESC while it runs to stop early.

Example

A site acceptance sheet is filtered to level 5 and its "Actual quantity" column must be filled from a take-off sheet, also filtered to level 5 but in a different row order.

Open the command, pick Paste values, tick Use condition column and click Continue. Source = A5:E200 on the "Take-off" sheet (column A is the work code, column E the quantity). Target = A5:H160 on the "Acceptance" sheet (column A is the work code, column H the actual quantity). The command matches each code and fills column H, skipping every hidden row on both sheets.

Notes

• Data is written straight over the target range and Ctrl+Z cannot undo it.
• Condition mode uses the first column of the selection as the key and its last column as the value — select the ranges so those two columns are the outer edges.
• Keys are compared case-insensitively; duplicate source keys mean the later value wins.
• Source rows with an empty key cell are skipped; target rows with no matching key keep their existing content.
• For merged cells the command reads and writes at the top-left cell, so the merge structure stays intact.
• Paste as formula reference writes an absolute reference including the source workbook and sheet name — renaming or moving the source file later breaks those formulas.

Related

• Select visible — check what is actually visible before copying.
• Merge rows by key — consolidate data per key before reconciling two tables.
• Delete hidden rows — remove hidden rows entirely when you no longer need them.