Macro to filter active ws in workbook and paste filtered result in "result" worksheet

I am trying without success to create a macro that will display filtered results in another worksheet. Attached is a copy of the file scenario. There are several macro attempts within the file but I cannot seem to set autofilter ranges

Basically for tyhe activesheet in workbook (except control sheet) the range a3 to last entry in column s or t needs to be filtered by column 3 with the criteria1 being "M" (for major work)
That filtered result would need to be pasted to the next available row in worksheet result, and then the autofilter to be turned off

explanation repeated in control sheet of workbook attached
Any help would be appreciated


I posted a different approach to the same problem in the Excel 2007 by mistake, and have been unable to remove, I hope the moderators will overlook this as I have tried to contact them to remove the post so I could repost here in the programming section

Many thanks Nigel

Hi
I have got as far as filtering the worksheet but have a problem. I need to copy only a few of the columns to another worksheet.

Is it possible to select specific areas of a filter result up to the last entry, or would it be better to copy this to an interim worksheet, delete the necessary columns, then copy onto the destination.

In the attached workbook "Macro2" will filter the data as required

worksheet "result" displays in yellow what needs to be copied

worksheet "result2" shows the information I require to paste to a different location.

Any help would be appreciated

Code of Macro2 so far

Please Login or Register  to view this content.

Getting nearer - have decided to copy filtered results from the active worksheet, rather than all sheets in workbook to simplify.

Code attached now filters selection as I want - and selects the data I require from the filter -

but I cant move it from there to the results sheets to delete columns before pasting to another workbook

am I doing something that it is stopping this from happening - any help appreciated - (run this macro in sheet 05c127860 of costing file from previous post to see result)

Please Login or Register  to view this content.

this now works for active worksheet (I decided on this as operator will see major work and update to other file accordingly)

how can I turn off autofilter at end of routine, I have tried several ways - macro works in vehicle costing workbook above

Please Login or Register  to view this content.