however I am trying to automate the process, it takes
too long to autofilter every sheet  and then copy and paste it to a
different sheet.

I currently use vba to merge  all sheets, but there has to be a way
of using vba to actually search find  then retrieve  them instead of
merging and then using a formula. So much easier pressing a button.