From the excel help files, this may be what your looking for:
Select the column or click a cell in the range or list you want to filter.
On the Data menu, point to Filter, and then click Advanced Filter.
Do one of the following.
To filter the range or list in place, similar to using AutoFilter, click Filter the list, in-place.
To copy the results of the filter to another location, click Copy to another location. Then, in the Copy To box, enter a cell reference.
To select a cell, click Collapse Dialog to temporarily hide the dialog box. Select the cell on the worksheet, and then press Expand Dialog .
Select the Unique records only check box.
Note Once you filter for unique values, you can copy them to another worksheet and then archive or delete the original worksheet.
But here's my problem. I can't filter data from one Sheet to a location on another sheet.
I'm copying and pasting a quickbooks report into one sheet. If I create an advanced sort in that sheet it will be over written when I copy in the new data. So I need the Sort function to happen on another sheet so when the data is copied in, the other sheet will update.
I have a feeling I new VBA to do this, I am a realtive novice to VBA though.