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.
- 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”.
- A new dialogue box named “Advanced Filter” will appear.
- Thirdly, check the option “Copy to another location”.
- Then click on the “List range” input box and go to the sheet named “Actual Sheet”.
- 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”.
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.
So, let’s take a look at the steps to apply the advanced filter option with VBA code to copy data to another worksheet.
- First, select the sheet “Copy Sheet-2”, where will copy our data.
- Next, right-click on the sheet and select the option “View Code”.
- 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.
- The above action will open a new dialogue box named “Copy to Another Sheet”.
- 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:
- 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.
Download Practice Workbook
You can download the practice workbook from here.
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.