VBA & Macro Problems

Copy Visible Filtered Cells to Another Sheet with Excel VBA

Tauseeq MagsiTauseeq Magsi Nov 18, 2026 869 views

Question details

Copy visible cells from filtered columns B:F to another worksheet as values starting at A2.

Copy Visible Filtered Cells to Another Sheet with Excel VBA
Product
Microsoft Excel
Device & OS
not provided
Scenario
A user manually filters an Excel table and wants to transfer the visible data to another worksheet using VBA.
Observed behavior
The destination should contain only visible table data, without formulas, beginning at A2.
Before you start

Filter the source table manually, make sure columns B:F are visible, and confirm that the table includes all five columns. Check the source sheet, destination sheet, and table names; the macro clears existing contents in A2:E on the destination sheet.

Solution 1Recommended

Use SpecialCells to copy visible table rows as values

Run this macro after filtering to copy visible data from B:F without the table header.

The macro uses xlCellTypeVisible to exclude filtered-out and manually hidden rows. PasteSpecial transfers values and number formats, not formulas.

1
Open the VBA editor

In desktop Excel, open the workbook and select Developer > Visual Basic. If the Developer tab is hidden, enable it in Excel's ribbon settings.

2
Insert the macro

Choose Insert > Module and paste the following code: Sub CopyVisibleTableValues() Dim wsSource As Worksheet, wsDest As Worksheet Dim tbl As ListObject Dim dataRange As Range, visibleRange As Range Set wsSource = ThisWorkbook.Worksheets("Source") Set wsDest = ThisWorkbook.Worksheets("Destination") Set tbl = wsSource.ListObjects("Table1") If tbl.DataBodyRange Is Nothing Then MsgBox "The source table has no data rows." Exit Sub End If Set dataRange = Intersect(tbl.DataBodyRange, wsSource.Range("B:F")) If dataRange Is Nothing Then MsgBox "The table does not include columns B:F." Exit Sub End If If dataRange.Columns.Count <> 5 Then MsgBox "The table must include all columns from B through F." Exit Sub End If On Error Resume Next Set visibleRange = dataRange.SpecialCells(xlCellTypeVisible) On Error GoTo 0 If visibleRange Is Nothing Then MsgBox "No visible data cells were found." Exit Sub End If wsDest.Range("A2:E" & wsDest.Rows.Count).ClearContents visibleRange.Copy wsDest.Range("A2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats Application.CutCopyMode = False End Sub

3
Match the workbook names

In the code, replace Source, Destination, and Table1 with your worksheet and table names. Select the source table and check Table Design > Table Name to find its name.

4
Run and check the result

Apply the desired table filter, then run CopyVisibleTableValues from Developer > Macros. Check the destination sheet from A2:E; the macro replaces any existing contents in that range.

5
Save the macro

Use File > Save As and choose Excel Macro-Enabled Workbook (.xlsm) if you want to keep the VBA code.

Use SpecialCells to copy visible table rows as values
Destination contents are cleared: The macro clears contents from A2 through the bottom of column E before pasting. It leaves row 1 intact and does not clear existing cell formatting.
Free Microsoft Office alternative

Work with filtered spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar interface for opening and editing spreadsheets. Its Excel format compatibility helps you move existing files into WPS; it does not fix Excel VBA issues, and macro compatibility should be checked separately.

Filter spreadsheet data with familiar table toolsOpen and edit Microsoft Excel XLSX filesUse a lightweight suite with a familiar Office-style interfaceMove existing spreadsheets into WPS without starting over
microsoft office alternative - wps office

Frequently Asked Questions

Does the macro copy the table header?

No. It uses the table's DataBodyRange, which excludes the header row, and starts pasting at A2.

Will formulas be copied to the destination?

No. PasteSpecial with xlPasteValuesAndNumberFormats pastes the displayed results as values along with their number formats.

What happens if the filter hides every data row?

SpecialCells finds no visible data range, so the macro displays a message and exits without clearing the destination. Adjust the table filter and run it again.

How do I find or change the table name?

Click a cell in the source table, open Table Design, and look for Table Name. Use that name in the macro's ListObjects reference.

Why should columns B:F be visible before running the macro?

xlCellTypeVisible excludes hidden columns as well as hidden rows. Unhide B:F before running the macro so all five columns are copied.