Copy Visible Filtered Cells to Another Sheet with Excel VBA
Question details
Copy visible cells from filtered columns B:F to another worksheet as values starting at A2.

- 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.
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.
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.
In desktop Excel, open the workbook and select Developer > Visual Basic. If the Developer tab is hidden, enable it in Excel's ribbon settings.
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
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.
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.
Use File > Save As and choose Excel Macro-Enabled Workbook (.xlsm) if you want to keep the VBA code.

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.

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.




