Posts by imanenigma

    I rewrote it a bit to use the variable instead of a variable for sheets and it works!

    I can't thank you enough!!

    Happy Monday!

    Assuming the sheets are named 1001, 1002 and 1003 and that you don't actually have over 1000 sheets in your workbook, you need Sheets("" & var1) and ActiveSheet.Range("$A$1:$G$1000").AutoFilter Field:=5, Criteria1:=var1


    Thank you! The Sheets("" & var1) works well!


    I am still getting an error on the AutoFilter Run-time error '438': Object doesn't support this property or method


    The autofilter is used to select based on criteria in this line ActiveSheet.Range("$A$1:$G$1000").AutoFilter Field:=5, Criteria1:=var1 if I put in "1001" it works as desired. Using the variable is the tough part.

    I am a pretty good Excel VBA hacker... I am not a trained programmer. I need help. I have a large set of data that needs to be filtered, copied, pasted and formatted on to 50+ worksheets.


    I am having trouble creating a for next loop with variables for the sheetname and filter name.


    This is the psuedo code that I cannot get to work. Apparently the variables within VBA either need to be objects or strings and I am not sure how to pass those. Getting errors on both the "Sheets("var1").Select" and ActiveSheet.Range("$A$1:$G$1000").AutoFilter Field:=5, Criteria1:="var1". If I manually update the variables shtnum and Filter1 and call them my code works. However, doing this manually for each of the 50 sheets is not a sensible way to program a repeatitive task.


    I appreciate any help that can be offered.

    Thanks!!!


    ----------------------------

    Using variables


    var1 (number from 1001 to 1050)

    shtnum for the sheet number

    Filter1 for the autofilterfilter variable

    data on worksheet named "data"


    pseudo code...


    -------------------



    :)