Skip to content

Excel: Copy Filtered Data to Another Worksheet Automatically | Advanced Filter (With VBA) & FILTER

    In this Microsoft Excel video tutorial I demonstrate how to copy filtered data to another worksheet automatically. I explore two methods:

    Click here to download the featured file

    METHOD 1: Use the Advanced Filter with VBA

    The advanced filter allows you to copy filtered results to another worksheet but it doesn’t update automatically if you change your data or criteria. You can however add VBA code to the filter which will update it automatically. 

    Here’s the VBA code used in the video (modified):

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Sheet4.Cells.Clear
Range("A1:E20").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("H1").CurrentRegion, _
CopyToRange:=Sheet4.Range("A1"), Unique:=False
End Sub

'code I used in the video: CriteriaRange:=Range("H1:I2")
'improved by using CriteriaRange:=Range("H1").CurrentRegion
'improved code will include new rows or columns added to the criteria 🙂

    METHOD 2: Use the FILTER Function (Excel 365)

    If you have Excel 365, you can use the FILTER function to copy filtered data to another worksheet. This method does not require any VBA and will update automatically when the data or criteria is changed.

    Video Table of Contents

    00:00 – Introduction

    00:15 – Using the Advanced Filter

    02:04 – Auto Update the Advanced Filter with VBA

    04:14 – Using the FILTER function