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

Get FREE Advanced Excel Exercises with Solutions!

Usually, the advanced filter option allows us to extract data from a data range with respect to multiple conditions. We can use the advanced filter in Excel to copy data to another sheet. Copy and paste data is a common and frequently used function while working in Excel. The use of the advanced filter to copy data to another sheet makes the process more effective. To illustrate the methods of this article we will use the same dataset for all the examples.


Advanced Filter to Copy Data to Another Sheet in Excel: 2 Ways to Use

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. In the second method, we will apply the VBA code to activate the advanced filter option. Then we will copy data to another sheet where we are using the VBA code.


1. Copy Data to Another Worksheet with Advanced Filter Feature

First and foremost, we will use the Advanced option from the excel ribbon to copy data to another sheet in excel with the advanced filter criteria. The Advanced option is available in excel 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 will copy the data to the sheet named “Copy Sheet” only for the salespersons with the last name Smith and Jones which is our criteria.

So, let’s see the steps to perform this action.

STEPS:

  • Firstly, go to the sheet named “Copy Sheet” where we want to copy the data.
  • Secondly, select the Data Click on the Advanced option from the section “Sort & Filter”.

Copy Data to Another Worksheet with Advanced Filter Feature

  • A new dialogue box named “Advanced Filter” will appear.

Copy Data to Another Worksheet with Advanced Filter Feature

  • Thirdly, check the option “Copy to another location”.
  • Then click on the “List range” input box and go to the sheet named “Actual Sheet”.

Copy Data to Another Worksheet with Advanced Filter Feature

  • Now, select the range (B4:D15).

  • We can see that the range (B4:D15) is selected in the “List range” In the input box, the value will look like the following one.

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

  • After that, set the following values in the “Advanced Filter” dialogue box-

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

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

  • Next, press OK.

  • Lastly, we can see that the above actions copy all the highlighted rows from the sheet named “Actual Sheet” to the sheet named “Copy Sheet”.

Copy Data to Another Worksheet with Advanced Filter Feature


2. Apply Advanced Filter with VBA Code to Copy Data to Another Worksheet

We can apply Advanced Filter with VBA code to copy data to another sheet. Basically in this method, we will do the previous task again but this time we will not use the Advanced option from the Data tab. Instead of that, we will apply a VBA code to activate the functionality of the advanced filter option.

Apply Advanced Filter with VBA Code to Copy Data to Another Worksheet

So, let’s take a look at the steps to apply the advanced filter option with VBA code to copy data to another worksheet.

STEPS:

  • First, select the sheet “Copy Sheet-2”, where will copy our data.
  • Next, right-click on the sheet and select the option “View Code”.

Apply Advanced Filter with VBA Code to Copy Data to Another Worksheet

  • Then, 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
  • Now press the F5 key or click on the Run to run the code.

Apply Advanced Filter with VBA Code to Copy Data to Another Worksheet

  • The above action will open a new dialogue box named “Copy to Another Sheet”.

Apply Advanced Filter with VBA Code to Copy Data to Another Worksheet

  • After that, click on the input box. Go to the sheet named “VBA” and select the range (B4:D15).
  • Press OK.

  • Then, another dialogue box will appear. We have to insert the value of the criteria range in the input box of that dialogue box. To insert the criteria click on the input box, select the sheet name VBA and from that sheet select the range (F9:F11).
  • The above actions will show a value in the input box like this:

VBA!$B$4:$D$15

  • Now press OK.

  • We can see one more dialogue box for the output range.
  • Select cell B4 from the sheet where we want to copy data. This will insert the value $B$4 in the input box.
  • Press OK

  • Finally, we get all the highlighted rows copied in another sheet.

Apply Advanced Filter with VBA Code to Copy Data to Another Worksheet


Download Practice Workbook

You can download the practice workbook from here.


Conclusion

In conclusion, this article is an overview to use Advanced Filter to copy data to another sheet in Excel advanced filter. For the best results, download, and practice using our practice workbook, which is attached to this post. If you have any questions, please leave a comment below. Our team will try to reply to you as soon as possible.


<< Go Back to Advanced Filter | Filter in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mukesh Dipto
Mukesh Dipto

Mukesh Dipto is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including Rhino3D, Maxsurf C++, AutoCAD, HTML, CSS, JavaScript, and WordPress. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo