Excel VBA AutoFilter: Copy Visible Results to Another Sheet

Yes. In Excel for Microsoft 365 on Windows, apply AutoFilter to the source range, then copy its visible cells to a destination; the macro below copies the header and matching rows. It is useful when you need a repeatable extract rather than manually copying the filtered list each time.

What AutoFilter Copies

AutoFilter hides rows that do not meet the selected criteria; it does not delete them. A normal copy of the entire source range can therefore include rows that are hidden by the filter, depending on how the selection is made. VBA can avoid that ambiguity by asking Excel for cells of type xlCellTypeVisible and copying only those cells.

The example below assumes a regular, contiguous data range beginning in cell A1 on a worksheet named Data. Row 1 contains headers, column C contains a Region field, and the destination worksheet is named Results. The macro filters Region for West, copies the visible header and rows, and places them at A1 on Results. Create both worksheets and confirm that the headers and data are in the expected positions before using it.

Save a copy of the workbook before running any macro: VBA changes cannot be undone with Ctrl+Z. This macro also clears the contents of the entire Results worksheet before writing the new extract.

Sub CopyWestVisibleRows()
    ' Copy the header and visible West rows from Data to Results.
    Dim wsSource As Worksheet
    Dim wsDest As Worksheet
    Dim dataRange As Range
    Dim visibleRange As Range

    Set wsSource = ThisWorkbook.Worksheets("Data")
    Set wsDest = ThisWorkbook.Worksheets("Results")
    Set dataRange = wsSource.Range("A1").CurrentRegion

    If dataRange.Rows.Count < 2 Then
        MsgBox "The source range has no data rows."
        Exit Sub
    End If

    ' Clear any existing filter criteria before applying this one.
    If wsSource.FilterMode Then wsSource.ShowAllData
    dataRange.AutoFilter Field:=3, Criteria1:="West"

    ' The visible range includes the header, even if no data rows match.
    Set visibleRange = dataRange.SpecialCells(xlCellTypeVisible)
    wsDest.Cells.ClearContents
    visibleRange.Copy Destination:=wsDest.Range("A1")
End Sub

Use Alt+F11 on Windows to open the Visual Basic Editor, then choose Insert > Module and paste the procedure into the module. On a Mac, Option+F11 generally opens the editor; some keyboards require Fn+Option+F11. Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm), and allow macros only when you trust the workbook and its code.

How the Code Finds and Filters the Data

Range("A1").CurrentRegion identifies the contiguous block around A1, bounded by blank rows and columns. That makes the example adapt to a changing number of records without hard-coding a last row. It also means a completely blank row or column inside the dataset can split the region, so keep the source table continuous or replace CurrentRegion with an explicitly defined range.

The Field argument in AutoFilter is a position within the range, not a worksheet column number. In this example, the range starts in column A, so Region in column C is field 3. If the range starts in column B, column C is field 2. Criteria1:=”West” requests an exact text match for that field. Change the criterion and field number to match your headers and reporting need.

Before filtering, the macro calls ShowAllData when the worksheet is currently filtered. That prevents an earlier selection on another field from silently narrowing the new results. The macro then applies the West criterion. It leaves the source filtered when it finishes; clearing the filter on the source does not change the copied extract.

Copying Only the Visible Rows

SpecialCells(xlCellTypeVisible) returns visible cells in the filtered range, including the header row. Copying that range to Results places the extracted rows together at the destination rather than retaining the source row numbers. If no records match, the visible header is still copied. This is often helpful because the output remains clearly labeled, but it is worth checking the row count if an empty extract would be mistaken for a successful result.

Copy transfers cell contents and formatting, and formulas are copied as formulas. Relative references in formulas can adjust when placed at the destination. If the extract should be a fixed snapshot of displayed values, copy the range and use Paste Special > Values instead of a regular copy, or add a values-only step to a macro designed for that purpose. Do not assume that a copied formula becomes a permanent value.

The destination is cleared with Cells.ClearContents, which removes values and formulas across the Results sheet but keeps formatting. This avoids leftover rows from a previous, longer extract. It can also erase other content on that worksheet, so reserve Results for generated output. If the sheet contains other material, clear only the output area rather than the whole sheet.

Adapting the Criteria and Destination

For another exact text match, change the criterion, for example from West to East. You can also filter a numeric field: Excel AutoFilter criteria can use comparison strings such as >=1000. The field number still refers to the position inside dataRange. For multiple conditions on one field, AutoFilter supports additional arguments such as Operator; choose the operator and criteria to match the comparison you need.

Change the source or destination: edit the worksheet names in the Set statements. The named worksheets must already exist in ThisWorkbook.
Change the field: count columns from the left edge of dataRange, with the first column as Field 1.
Change the output start cell: replace Results.Range(“A1”) with another destination cell, and adjust clearing so it does not erase unrelated content.
Keep the source unfiltered afterward: call ShowAllData after the copy, provided the worksheet is filtered. If you need to preserve a user’s pre-existing filter settings exactly, design the procedure to record and restore those settings rather than simply clearing them.

For a different criterion each run, a macro can read the desired value from a worksheet cell instead of embedding “West” in the procedure. Validate that input and make the cell location clear to users. When a filter value comes from a text box or user input, avoid building executable VBA code from that text; pass it as a filter criterion instead.

Using Excel Tables Instead of a Plain Range

If the source is an Excel Table, its size expands as rows are added and its headers are managed as part of the table. A table is often a better source for a recurring report than a manually maintained range. VBA can apply AutoFilter to the table’s range, using a field number relative to the table. Make sure the table’s header row and field order match the code, and avoid applying a second, conflicting filter to the same data.

The sample uses CurrentRegion because it is concise for a simple range, not because every workbook has that layout. A sheet with notes beside the data, blank separator columns, or multiple blocks can cause CurrentRegion to include too much or too little. In those cases, refer to a named range, a known table, or a deliberately calculated last row and last column.

Manual Copying and Other Approaches

For a one-time task, you may not need VBA. Filter the data with Data > Filter, select the filtered range, and use Home > Find & Select > Go To Special > Visible cells only before copying. On Windows, Alt+; selects visible cells only in the current selection. Then copy and paste the result. This extra selection step helps prevent hidden rows from being included in a manual copy.

VBA is more suitable when the same extract must be produced regularly, when several criteria or sheets are involved, or when a button should generate a consistent output. Power Query can be a better fit for repeatable data preparation that needs refreshable steps, particularly when the source data changes and the output should be rebuilt without a macro. A formula-based report can also work for dynamic results in Microsoft 365, but formulas and VBA serve different purposes: formulas recalculate in the worksheet, while this macro writes a copied extract to another sheet.

Common Problems and Checks

Subscript out of range: a worksheet name in the code does not exactly match a tab in the workbook, or the sheet is in a different workbook. The code uses ThisWorkbook, meaning the workbook that contains the macro.
Wrong field filtered: Field counts from the first column of dataRange, not from worksheet column A. Check the range’s left edge and header order.
Incomplete output: a blank row or column may have cut CurrentRegion short. Inspect the region or use a table or explicit range.
Old rows remain: clearing only part of the destination can leave rows from a previous, larger result. The example clears Results contents before copying, so keep that sheet dedicated to the extract.
Macro does not run: confirm the file is saved as .xlsm and that macros are enabled under the organization’s security policy. Do not enable code from an unknown source.

For an unexpected result, first check the filter arrows and source headers, then verify the number of visible records before copying. Also confirm that the Results sheet is not protected in a way that blocks clearing or pasting. If the workbook uses an Excel Table, merged cells, or multiple separate filter ranges, adapt and test the procedure on a copy rather than assuming the simple-range example covers those layouts.

Frequently Asked Questions

Does AutoFilter delete the rows that do not match? No. AutoFilter hides nonmatching rows; the source data remains in the worksheet. The macro copies visible cells to Results and does not delete the hidden source records.

Does the macro copy the header row? Yes. The source range begins at A1 and includes its header, and the visible-cell selection includes that header. If the destination should contain data only, exclude the header row deliberately rather than removing it accidentally.

What happens when no rows match? The header remains visible, so the example copies the header without matching data rows. Add a separate check or message if your workflow needs to distinguish an empty result from a populated extract.

Can I copy the results to a new workbook? Yes. Set a destination worksheet variable to a worksheet in the other workbook and ensure that workbook is open. The sample uses ThisWorkbook for both sheets, which keeps its destination in the workbook containing the macro.

Can I undo a VBA copy with Ctrl+Z? No. Running a macro clears and writes cells outside Excel’s normal undo history. Save a separate copy before running it, and test changes on sample data first.

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.