What VBA variables can I use with Excel "sheets("var1").select" and "ActiveSheet.Range("$A$1:$G$1000").AutoFilter Field:=5, Criteria1:="var1"" statements?

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


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



    :)

  • 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

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

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

  • You won't get a 438 error on that line unless your active sheet is a chart. Which line is actually causing the error?

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • 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!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!