Copying data from one sheet to another in Excel can also be done with the help of Advanced Filter.
Let’s assume you have a list of orders recorded in the ‘Original’ worksheet and you want to copy the ones above $400 in the ‘Top Orders’ worksheet.
First of all set up the filter condition in cells E2 and E3 of the ‘Original’ sheet.
You need to specify the filter criteria (e.g. ‘Order’) and its values (e.g. ‘>400′).
Next, in the ‘Top Orders’ sheet (the destination worksheet), set up the column headings corresponding to the information that you want to copy across.
You can have all or just a few of the original range’s column headings however you do need to include the one on which you are applying the filter (in our case ‘Order’).
In our example, I have created the headings in cells A3 and B3 of the ‘Top Orders’ sheet.
Next, make sure you have selected a cell outside the destination range for your copied cells and select ‘Advanced Filter‘.
You find the Advanced Filter in the ‘Data’ tab of the Excel menu.
In the ‘Action’ select ‘Copy to another location‘. This will allow you to copy data to and from another sheet in Excel.
For ‘List Range:’ select the original data range that you want to copy across.
In our case it’s the range contained within ‘Original!$A$2:$B$674′.
The criteria range is the one that we have set up in range ‘Original!$E$2:$E$3′. It includes the column heading and the filter value (i.e. ‘Order’ and ‘>400′).
In ‘Copy to:’ insert the destination for the copied data. You just need to select the column headings. In our case, ”Top orders’!$A$3:$B$3′.
This procedure allows you to automatically copy data from one sheet to another using advanced filter to obtain only the relevant information according to your needs.
All of this without the need for a macro!
Remember that the destination data is not linked to the original range so if you change the original data you will need to re-run the filter.