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.



    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...