How to Use Advanced Filter to Copy Data to Another Sheet in Excel

In this article, we will demonstrate two methods to apply the Advanced Filter to copy data to another sheet in Excel. In the first method, we will use the Advanced option from the Data tab, and in the second, we will apply VBA code to activate the Advanced Filter option, then copy data to a different sheet.

Method 1 – Copying Data to Another Worksheet with the Advanced Filter Feature

We can use the advanced filter criteria in the Advanced option on the Excel ribbon to copy data to another sheet in Excel. The Advanced option is located in the Data tab under the section named “Sort & Filter”.

In the following dataset, we have the “Last Name”, “Sales Amount”, and City for different salespersons. We’ll copy the data only for the salespersons with the last name Smith and Jones to a sheet named “Copy Sheet”.

Steps:

Go to the sheet named “Copy Sheet” (the destination sheet, where the data will be pasted).
Go to the Data tab in that sheet.
Click on the Advanced option from the section “Sort & Filter”.

A new dialog box named “Advanced Filter” will appear.

Check the option “Copy to another location”.
Click on the “List range” input box and go to the sheet named “Actual Sheet”.

In that sheet, select the range B4:D15.

The range (B4:D15) is selected in the “List range” input box, whose value should look like this:

List range: ‘Actual Sheet’!$B$4:$D15

Set the following values in the “Advanced Filter” dialog box:

Criteria range: ‘Actual Sheet’!$F$9:$F$11

Copy to: ‘Copy Sheet’!$B$4

Click OK.

The highlighted rows are copied from the sheet named “Actual Sheet” to the sheet named “Copy Sheet”.

Method 2 – Using the Advanced Filter with VBA Code to Copy Data to Another Worksheet

We can also apply Advanced Filter with VBA code to copy data to another sheet. Basically, in this method we will do exactly the same thing as in the first Method, but this time apply a VBA code to activate the functionality of the Advanced Filter option.

Steps:

Select the destination sheet “Copy Sheet-2”.
Right-click on the sheet name and select the option “View Code”.

A blank VBA module will appear.

Insert the following code in the blank module:

Sub Advance_Filter_to_Copy_to_Another_Sheet()
Dim Str As String
Dim Address As String
Dim Rg As Range
Dim CRg As Range
Dim SRg As Range
On Error Resume Next
xAddress = ActiveWindow.RangeSelection.Address
Set Rg = Application.InputBox("Please select the filter range:", "Copy to Another Sheet", xAddress, , , , , 8)
If Rg Is Nothing Then Exit Sub
Set CRg = Application.InputBox("Please select the criteria range:", "Copy to Another Sheet", "", , , , , 8)
If CRg Is Nothing Then Exit Sub
Set SRg = Application.InputBox("Please select the output range:", "Copy to Another Sheet", "", , , , , 8)
If SRg Is Nothing Then Exit Sub
Rg.AdvancedFilter xlFilterCopy, CRg, SRg, False
SRg.Worksheet.Activate
SRg.Worksheet.Columns.AutoFit
End Sub