advance filter sustitution in a macro - shared workbook
 

I wanted to add a control button that will filter in place a set of criteria.

If Column A title is STATUS = and the results in the field = Completed,
passed, Archive, Done
then I want the rows of data to be placed in a new workbook, ie book 1.

So when someone selects the command button they will get all of the statuses
above copied into a new workbook.

i created a macro and assigned it to the comand button - usied advanced
filter and used the criteria results above, once the filter ran, i then
copied the sheet and pasted it in a new workbook.

The problem i have is that the button doesnt work when i turn the workbook
on to "shared" due to having the advanced filter used in the macro.

Is there another way i can write the macro to not use advanced filter?