How to copy data to another worksheet with Advanced Filter in Excel?
Within Excel, the Advanced Filter feature is an effective tool for extracting and filtering data according to your criteria. It is commonly used within the same worksheet, but you may notice that when trying to copy filtered results directly onto a different worksheet, Excel presents a warning dialog. This issue can be frustrating, especially for users managing data across multiple sheets such as larger reports or dashboards. Being able to transfer filtered data accurately to another worksheet supports clean data presentation, simplifies sharing, and maintains organized records.
Copy data to another worksheet with Advanced Filter by using Advanced Filter feature
Copy data to another worksheet with Advanced Filter by using VBA code
Copy data to another worksheet with Advanced Filter by using Advanced Filter feature
Excel will not allow you to use the Advanced Filter to copy filtered results directly onto a different worksheet from the one where the data resides. However, you can overcome this limitation by following a practical workaround that involves activating the destination worksheet before running the filter. The steps below will help you solve this challenge without complex setups or specialized tools. This approach is ideal for users who want to filter and move data manually and for one-off or simple list transfers. However, if you need to automate frequent filtering and copying across sheets, consider using VBA code, which is explained later in the guide.
1. Start by activating the worksheet to which you wish the filtered results to be copied. This triggers Excel to output the filtered data into your selected sheet while applying the Advanced Filter. Make sure the worksheet does not contain any existing data where you intend to paste, to prevent overwriting.
2. With the destination worksheet active, go to the Excel ribbon, click Data, then choose Advanced in the Sort & Filter group. This opens the Advanced Filter dialog, where you can fine-tune the filtering process. Double-check that you are on the correct worksheet before continuing, as the filtered output will appear here.
3. In the Advanced Filter dialog box:
- Select Copy to another location under Action.
- Click
beside List range to select the dataset you want to filter. You can navigate to the source worksheet during this step. - Click beside the Criteria range to specify the criteria for filtering. The criteria can be located on any sheet.
Tips: The List range should include headers so that the criteria are matched correctly. The Criteria range must also have matching headers and contain the logic for filtering (e.g., values, text, or formulas). To avoid errors, confirm that your criteria are well-formed and that the ranges do not overlap.
4. Next, click 
beside the Copy to field. Here, select the cell in your active worksheet where you want the filtered results to start appearing. After your selections are made, click OK. Excel will transfer the filtered data from the source to your current, activated sheet.
Notes and troubleshooting:
- If your criteria do not yield any matches, Excel will copy only the column headers to the target worksheet.
- Always activate the target worksheet before triggering Advanced Filter; otherwise, Excel will produce a warning and block cross-sheet copying.
- If the output range overlaps with existing data, results may be overwritten without further prompts.
- For large datasets, filtering and copying may take several seconds. Wait for Excel to finish processing before editing further.
- If error messages appear, check your List and Criteria ranges for blank rows, mismatched headers, or merged cells, which can interfere with filtering.
This manual method works best for occasional or simple tasks and provides immediate feedback if something goes wrong. For more complex workflows or repeated tasks, automating with VBA will save time and reduce errors, as shown below.
Copy data to another worksheet with Advanced Filter by using VBA code
For users who work frequently with cross-sheet filtering or require more robust automation, VBA (Visual Basic for Applications) offers an efficient way to transfer filtered data between worksheets, bypassing the limitations of the standard interface. This solution is particularly useful in scenarios involving repetitive data management or reports that require consistent filter logic.
1. Begin by activating the worksheet containing the data you wish to filter and copy. This sets the context for the code execution and selection prompts.
2. Press ALT + F11 in Excel to open the Microsoft Visual Basic for Applications editor.
3. In the VBA editor window, click Insert > Module to add a new module. Then copy and paste the following code into the module window:
VBA code: Copy data to another worksheet with Advanced Filter:
Sub Advancedfiltertoanothersheet()
'Updateby Extendoffice
Dim xStr As String
Dim xAddress As String
Dim xRg As Range
Dim xCRg As Range
Dim xSRg As Range
On Error Resume Next
xAddress = ActiveWindow.RangeSelection.Address
Set xRg = Application.InputBox("Please select the filter range:", "Kutools for Excel", xAddress, , , , , 8)
If xRg Is Nothing Then Exit Sub
Set xCRg = Application.InputBox("Please select the criteria range:", "Kutools for Excel", "", , , , , 8)
If xCRg Is Nothing Then Exit Sub
Set xSRg = Application.InputBox("Please select the output range:", "Kutools for Excel", "", , , , , 8)
If xSRg Is Nothing Then Exit Sub
xRg.AdvancedFilter xlFilterCopy, xCRg, xSRg, False
xSRg.Worksheet.Activate
xSRg.Worksheet.Columns.AutoFit
End Sub4. After inserting the code, run the macro by pressing the F5 key. You will see a prompt requesting you to choose the filter range (the data with headers that you wish to filter). Select your desired range and click OK.
5. The next dialog will prompt you to select the criteria range. This should also include headers and contain the criteria for filtering. Select the criteria cells and click OK.
6. A final dialog will appear asking you to select the output range in the destination worksheet. You can navigate to a different sheet if needed, then select the cell where you want the filtered output to begin. Press OK after your selection.
7. The filtered data is copied automatically to the specified location in your chosen worksheet. The columns in the destination sheet will auto-adjust for width. You will not lose any original data in either worksheet unless you choose a destination that contains existing content, in which case it may be overwritten.
- Precautions:
- Ensure your filter and criteria ranges both include headers and do not contain merged or blank rows to avoid errors.
- Check that your target cell for output is positioned to accommodate the filtered dataset size, or you risk overwriting adjacent data.
- If you need to repeat this process often, you can save the macro and assign a shortcut for quicker access.
- If you encounter errors or the macro does not run as expected, confirm that macros are enabled and your workbook is saved as a macro-enabled file (.xlsm).
Troubleshooting & Tips:
- If you select ranges incorrectly or leave them blank, the macro will exit without changes—simply re-run and reselect the correct areas.
- When working with large datasets, wait for the process to complete before performing further actions.
- You can modify the VBA to include more advanced logic, such as removing duplicates or copying entire rows.
In summary, both solutions allow you to copy filtered data to another worksheet using Advanced Filter, with manual methods best for simple or low-frequency tasks and the VBA approach recommended for regular, automated data handling scenarios. Choosing the optimal approach will depend on your workflow, technical comfort level, and whether you need to repeat these operations often.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in