When you use the Advanced Filter feature in Microsoft Excel to copy filtered data to another location, the data is not copied. Additionally, you receive the following error message:
You can only copy filtered data to the active sheet.
This issue may occur if the location to which you want to copy the filtered data is not the active worksheet
To resolve this issue, move the insertion point to the location at which you want to copy the filtered data. Then, use the Advanced Filter feature to copy the filtered data. To do this, follow these steps.
Note For the steps in this section, use the data from the following table in the Excel worksheet.
Note Cells A9 and A10 are the criteria range that you will use when you copy the data to a different location.
Open the worksheet to which you want to copy the filtered data. If the worksheet is located in a different workbook, open that workbook, and then open the appropriate worksheet.
Note The worksheet to which you want to copy the filtered data must be the active worksheet.
If you want to copy only selected fields from the filtered data, follow these steps before you go to step 2:
Copy the heading row of the filtered data that you want to copy to another location, and then paste the row in the location in the active worksheet at which you want the data to appear.
Select the heading row that you pasted in step 1a, and then go to step 2.
On the Data menu, point to Filter, and then click Advanced Filter.
In the Advanced Filter dialog box, follow these steps:
Click Copy to a different location.
In the List range box, click List range, switch to the worksheet that contains the data that you want to copy, and then select that data range.
The List range box is automatically filled with the data range that you are copying.
In the Advanced Filter: List range dialog box, click List range.
In the Criteria range box, click Criteria range, and then select the criteria data. If the criteria data is located on a different worksheet, you must switch to the worksheet that contains the criteria data.
The Criteria range box is automatically filled with the data range of the criteria data.
In the Advanced Filter - Criteria range dialog box, click Criteria range.
In the Copy to box, click Copy to, and then click a cell in the active worksheet that you opened in step 1. This cell is the location at which you want the filtered data to be copied.
The Copy to box is automatically filled with the data range into which you want to copy the filtered data.
In the Advanced Filter - Copy to dialog box, click Copy to.