VBA Macro for filtering

  • HI!

    I've created the macro below for the purpose of some reporting.

    It works in the following way:

    1. User opens the UserForm, when he or she can select the relevant column name (col) and value to look for (strName).

    2. Macro should now loop thourght the specific sheets and for each and every case, when it finds a match - simply copy/paste visible cells into new file/separate sheets.

    Now the 2 problems, which I have with this macro occurs at line:

    1. .AutoFilter Field:=cfind.Column, Criteria1:="*" & strName & "*"

    1. I can't add more than 3 sheets to an array, as I receive error: "autofilter method of range class failed"

    2. Macro for some reason copies the data from 1st sheet correctly, however for the rest of spreadsheets it copies only headers (without underling data)

    I'd appreciate some suggestions on how this can be fixed.

    Many thanks!

    My code:

  • Hi, I've figured it on my own. It was so easy, that I am actually feeling stupid :P.

    Full code below: