Excel VBA: Copy and Paste Filtered Cells Only Safely

Yes. In Excel VBA, use SpecialCells(xlCellTypeVisible) on the filtered data range to select cells that remain visible, then copy those cells to a separate destination. For a filtered Excel table, the example below copies the header and visible rows to another worksheet; it assumes the source table’s columns are not manually hidden.

What “visible cells only” means

An AutoFilter hides rows that do not meet its criteria; it does not delete those rows. A normal copy of the complete source range can therefore include filtered-out records. The VBA constant xlCellTypeVisible asks Excel to return cells that are currently visible, so filtered-out rows are left out of the selection.

Visibility can also be affected by manually hidden rows and columns. SpecialCells(xlCellTypeVisible) excludes hidden columns as well as hidden rows. That matters if you want every field in each matching record: unhide the table’s columns first, or use a row-by-row method that copies the full table row. The first macro below is intended for the common case where the table’s columns are all visible.

Copying visible cells is different from copying only values. A regular copy carries formulas, formatting, and other cell content as Excel normally does. If you want a plain data extract without formulas or formatting, use the values-only approach later in this article.

Copy visible table rows with VBA

This macro uses a named Excel table, copies its header, and then copies each visible block of table data to a worksheet named Extract. It writes the output starting in cell A1. Create the destination worksheet and table first, and change the worksheet or table names in the code if your workbook uses different names.

Sub CopyVisibleTableRows()
    Dim wsSource As Worksheet
    Dim wsOutput As Worksheet
    Dim tbl As ListObject
    Dim visibleData As Range
    Dim area As Range
    Dim nextRow As Long

    ' Set these names to match the workbook.
    Set wsSource = ThisWorkbook.Worksheets("Data")
    Set wsOutput = ThisWorkbook.Worksheets("Extract")
    Set tbl = wsSource.ListObjects("Table1")

    ' A table with headers but no data has nothing to copy.
    If tbl.DataBodyRange Is Nothing Then Exit Sub

    ' SpecialCells raises an error when no data cells are visible.
    On Error Resume Next
    Set visibleData = tbl.DataBodyRange.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0

    If visibleData Is Nothing Then
        MsgBox "There are no visible data rows to copy.", vbInformation
        Exit Sub
    End If

    ' Copy the header, then append each visible block below it.
    tbl.HeaderRowRange.Copy Destination:=wsOutput.Cells(1, 1)
    nextRow = 2

    For Each area In visibleData.Areas
        area.Copy Destination:=wsOutput.Cells(nextRow, 1)
        nextRow = nextRow + area.Rows.Count
    Next area
End Sub

To set it up, press Alt+F11 on Windows to open the Visual Basic Editor, choose Insert > Module, and paste in the macro. Return to Excel and run it from Developer > Macros. If the Developer tab is not visible, enable it in Excel’s ribbon preferences. On a Mac, open the Visual Basic Editor from the Developer tab or use the Mac’s assigned Visual Basic Editor shortcut; keyboard shortcuts can differ by keyboard and Excel settings.

The code copies each contiguous visible block in order, so gaps left by filtered-out rows do not appear in the output. It copies cell contents and formatting in the same way as a normal Excel copy. It does not clear the destination worksheet first: old output below or beside the new results can remain, so clear the intended output area yourself if you need a fresh extract.

Using a regular filtered range instead of a table

Excel tables make it straightforward to refer to the header and data separately. If the source is an ordinary range with headers in row 1, first identify the complete range covered by the filter. For example, if the filter range is A1:D100, the data portion starts at A2. Apply SpecialCells(xlCellTypeVisible) to that data portion, not to the whole worksheet. Otherwise, unrelated visible cells elsewhere on the sheet could be included.

For a range-based macro, set a variable to the data rows, excluding the header, and apply SpecialCells(xlCellTypeVisible) to it. Copy the header separately, then copy the visible data range. The same two cautions apply: handle the case where no rows match, and avoid hidden columns if you need a complete set of fields. A table is usually less fragile than hard-coding a final row because its data range expands as records are added.

If the filter is applied to a multi-column range, keep the source selection rectangular and aligned with the destination’s first column. Avoid selecting only a few scattered columns and assuming Excel will paste them as one intact record; multi-area selections can behave differently from a single contiguous range.

Copying values only, including hidden columns

If the goal is a clean list of matching records, assigning values row by row avoids clipboard and multi-area copying issues. This alternative checks whether each table data row is visible, then writes the entire row’s values to the output. It includes hidden columns because it reads the full table row, but it does not preserve formatting or formulas: formula cells become their current results.

Sub CopyVisibleTableValues()
    Dim wsSource As Worksheet
    Dim wsOutput As Worksheet
    Dim tbl As ListObject
    Dim sourceRow As Range
    Dim outputRow As Long

    ' Set these names to match the workbook.
    Set wsSource = ThisWorkbook.Worksheets("Data")
    Set wsOutput = ThisWorkbook.Worksheets("Extract")
    Set tbl = wsSource.ListObjects("Table1")

    ' Copy the header as values, then write visible data rows.
    wsOutput.Cells(1, 1).Resize(1, tbl.ListColumns.Count).Value = _
        tbl.HeaderRowRange.Value
    outputRow = 2

    If tbl.DataBodyRange Is Nothing Then Exit Sub

    For Each sourceRow In tbl.DataBodyRange.Rows
        If Not sourceRow.EntireRow.Hidden Then
            wsOutput.Cells(outputRow, 1).Resize(1, tbl.ListColumns.Count).Value = _
                sourceRow.Value
            outputRow = outputRow + 1
        End If
    Next sourceRow
End Sub

Because the test uses EntireRow.Hidden, rows hidden by the filter are skipped, as are rows hidden manually. That is appropriate when “visible only” means rows a person can currently see. The macro copies calculated values, not formulas, so formulas in the output will not recalculate from references in the source workbook.

How to choose the right copy method

Choose based on what the destination needs to contain, rather than assuming every filtered extract should be pasted the same way:

Keep formulas and formatting: use the copy-based macro. Check that formulas copied to the output still make sense there, because relative references can adjust during copying.
Make a static report or export: use the values-only macro. It is less likely to bring unwanted formulas or source formatting along.
Copy selected fields only: define the desired output columns explicitly and map them to the destination. Do not rely on a noncontiguous selection to paste into a compact layout automatically.
Keep the filtered result in place: there may be no need to copy at all. Work with the filtered table directly, or export it only when a separate, stable result is required.

For a one-time manual task, select the source range, use Home > Find & Select > Go To Special > Visible cells only, and copy. On Windows, Alt+; is the shortcut for selecting visible cells only after selecting a range. Then paste into the destination. The VBA approach is useful when the same task must be repeated, when the destination is predictable, or when a larger process needs to run without relying on manual selection.

Common errors and how to fix them

“No cells were found” or a run-time error: no data rows may currently be visible. Check the filter criteria and make sure the range excludes the header before calling SpecialCells. The sample macro handles the no-visible-data case.
Only some columns appear: xlCellTypeVisible also leaves out manually hidden columns. Unhide those columns before running the copy-based macro, or use the values-only row loop.
Old records remain in the output: the examples do not erase previous results. Clear the destination area before running, taking care not to remove unrelated content.
Output is split or misaligned: a visible selection can consist of multiple separate areas. Copying each area individually is why the first macro advances the destination row after each block. If you have hidden columns or want a custom column order, use explicit row and column mapping instead.
Formulas show unexpected results: copied formulas can update relative references based on their new location. Use a values-only output if the result should be fixed, or review formula references before relying on the copied sheet.

Safety and workbook considerations

Save a copy of the workbook before running a macro. VBA actions cannot be undone with Ctrl+Z, and a later change to a macro could overwrite or clear output that matters. The examples here do not delete source records, but the destination can still be changed by copying over cells.

Macros must be allowed to run for VBA code to execute. Use code only in workbooks you trust, and follow your organization’s macro-security rules rather than enabling macros indiscriminately. If the workbook contains sensitive data, remember that copying filtered rows to another sheet creates another copy of that information; filtering is not a privacy control.

These instructions target Excel for Microsoft 365 on Windows. Excel for the web can open workbooks containing VBA but cannot run VBA macros. Excel for Mac supports VBA, although editor access and keyboard shortcuts may differ; the macro logic is intended for desktop Excel, and the sample’s worksheet and table names must still match the workbook.

Frequently Asked Questions

How do I copy only visible cells in Excel VBA?
Apply SpecialCells(xlCellTypeVisible) to the filtered data range, then copy the resulting range. Exclude the header if you plan to copy it separately, and handle the possibility that no data rows are visible.

Does SpecialCells(xlCellTypeVisible) skip filtered-out rows?
Yes. It selects cells that are visible at the time the code runs, so rows hidden by an active filter are excluded. It also excludes manually hidden rows and hidden columns.

Can I paste filtered rows into a continuous list?
Yes. Copy visible blocks one after another and advance the output row by the number of rows in each block, as in the first macro. A row-by-row values-only loop is another way to create a continuous result.

How do I copy filtered rows without formulas?
Assign each visible row’s .Value to the destination rather than using Copy. The values-only macro does this, so formula cells are transferred as their current calculated values.

Why does my macro fail when no rows match the filter?
When there are no visible data cells, SpecialCells can raise an error instead of returning a usable range. Use local error handling around that call and test whether the range variable is Nothing before copying.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.