var kba = new KBAlertz { "Launch Date" = "October 30th, 2013" }

Article ID: 909527 - View products that this article applies to.

SYMPTOMS

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.

CAUSE

This issue may occur if the location to which you want to copy the filtered data is not the active worksheet

RESOLUTION

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.
Collapse this tableExpand this table
ABCDE
1LastFirstAddressCityState
2JonesJames132 SkywaySkagwayAK
3JonesBill135 SkywayNomeAK
4JonesFrank139 SkywaySkaklutAK
5JonesBill135 SkywayNomeAK
6JonesJames137 SkywaySiberiaUT
7
8
9State
10AK
Note Cells A9 and A10 are the criteria range that you will use when you copy the data to a different location.
  1. 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:
    1. 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.
    2. Select the heading row that you pasted in step 1a, and then go to step 2.
  2. On the Data menu, point to Filter, and then click Advanced Filter.
  3. In the Advanced Filter dialog box, follow these steps:
    1. Click Copy to a different location.
    2. 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.
    3. In the Advanced Filter: List range dialog box, click List range.
    4. 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.
    5. In the Advanced Filter - Criteria range dialog box, click Criteria range.
    6. 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.
    7. In the Advanced Filter - Copy to dialog box, click Copy to.
  4. In the Advanced Filter dialog box, click OK.

Properties

Article ID: 909527 - Last Review: December 7, 2005 - Revision: 2.2
APPLIES TO
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
Keywords: 
kberrmsg kbtshoot kbprb KB909527