VBA & Macro Problems

Excel VBA AutoFilter: Copy Rows Only When Departments Match

Nimra MalikNimra Malik Nov 22, 2026 868 views

Question details

Copy filtered Excel rows between worksheets only when one or more department criteria match.

Copy Excel AutoFilter Rows Only When Departments Match
Product
Microsoft Excel VBA
Device & OS
not provided
Scenario
A VBA procedure filters a source worksheet for multiple departments and copies matching records to another worksheet as values.
Observed behavior
The procedure needs to avoid copying data when no department records match the filter.
Before you start

Identify the source table, its department column, and the destination cell. The example assumes headers in row 1, departments in column B, and a contiguous table starting at A1; unhide any source columns you need to copy.

Solution 1Recommended

Copy visible AutoFilter data rows after checking for matches

Use this macro to filter for multiple departments, exclude the header, and copy only visible data rows as values.

The helper function returns Nothing when no visible data cells are found. The macro exits before copying in that case. Existing AutoFilters on the source sheet are removed before the new filter is applied.

1
Open the VBA editor

In Excel, open the workbook and select Developer > Visual Basic. In the VBA editor, select Insert > Module.

2
Add the filtering macro

Paste this code into the module: Sub CopyMatchingDepartments() Dim ws As Worksheet, dest As Worksheet Dim tableRange As Range, source As Range Set ws = Worksheets("Prog_2") Set dest = Worksheets("Destination") Set tableRange = ws.Range("A1").CurrentRegion If tableRange.Rows.Count < 2 Or tableRange.Columns.Count < 2 Then Exit Sub If ws.AutoFilterMode Then ws.AutoFilterMode = False tableRange.AutoFilter Field:=2, _ Criteria1:=Array("Sales", "Finance"), Operator:=xlFilterValues Set source = VisibleFilterData(ws) If source Is Nothing Then Exit Sub source.Copy dest.Range("A2").PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False End Sub Function VisibleFilterData(ws As Worksheet) As Range Dim filteredRange As Range, dataRange As Range If Not ws.AutoFilterMode Then Exit Function Set filteredRange = ws.AutoFilter.Range If filteredRange.Rows.Count < 2 Then Exit Function Set dataRange = filteredRange.Offset(1, 0).Resize(filteredRange.Rows.Count - 1) On Error Resume Next Set VisibleFilterData = dataRange.SpecialCells(xlCellTypeVisible) On Error GoTo 0 End Function

3
Adjust the workbook references

Replace "Prog_2" and "Destination" with your worksheet names. Change Field:=2 to the department column's position within the source table, and edit the department names in the Criteria1 array. Change dest.Range("A2") if needed.

4
Run and check the result

Select Developer > Macros, choose CopyMatchingDepartments, and select Run. Test both matching and nonmatching department names; the destination should receive values only when visible data rows exist.

Existing destination data: A no-match result leaves the destination unchanged. The macro does not clear records from an earlier run or copy the source header.
Free Microsoft Office alternative

Filter department data with WPS Spreadsheets

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface for filtering department records. It supports common Excel formats for easier migration, but it does not fix Excel VBA macros; check macro compatibility before relying on them.

Filter spreadsheet records by department using a familiar interfaceOpen and edit common Excel formats, including XLSXUse a free, lightweight office suiteMove spreadsheet work into a familiar tabbed workspace
microsoft office alternative - wps office

Frequently Asked Questions

Why exclude the header before checking for visible cells?

The AutoFilter header normally remains visible even when no data rows match. The helper removes the first row from AutoFilter.Range before calling SpecialCells.

How do I filter for more than two departments?

Add department names to Criteria1:=Array("Sales", "Finance") and keep Operator:=xlFilterValues. Use the department column's position within the filtered range for Field.

What happens if no department matches?

SpecialCells finds no visible data cells, so the helper returns Nothing. The If source Is Nothing Then Exit Sub check stops the procedure before the copy.

Does the macro clear old destination rows?

No. It pastes matching values starting at Destination!A2 and leaves the destination unchanged when there are no matches. Clear the appropriate destination range separately if your workflow requires it.