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

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

Sub ParseData()
For var1 = 1001 To 1003
'select sheet
shtnum = Sheets("var1").Select
'autofilter based on var1
Sheets("data").Select
Range("A1").Select
ActiveCell.SelectSelection.AutoFilter
ActiveSheet.Range("$A$1:$G$1000").AutoFilter Field:=5, Criteria1:="var1"
'select populated cells from autofilter
ActiveCell.Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
'copy data
Selection.Copy
'Create Sheet = "var1"
Sheets.Add.Name = "var1"
'select sheet (newly created sheet from above)
shtnum
' paste data
Range("A3").Select
ActiveSheet.Paste
' format data (there are many more formats below is an example)
-		ActiveCell.FormulaR1C1 = "Column1"
Range("B3").Select
ActiveCell.FormulaR1C1 = "Column2"
Columns("A:A").columnwidth = 8
Columns("B:B").columnwidth = 60
Cells.Select
Cells.EntireRow.AutoFit
Next
end sub

Quote from rory

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.

Yes, in the code I create the sheet then move to the data autofilter the data then move back to the sheet and paste.

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!

Sub ParseData()
For var1 = 1001 To 1003
'Create Sheet = "var1"
Sheets.Add.Name = var1
'autofilter based on var1
Sheets("data").Select
Range("$A$1:$G$1000").Select
ActiveSheet.Range("$A$1:$G$232").AutoFilter Field:=5, Criteria1:=var1
'select populated cells from autofilter
ActiveCell.Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
'copy data
Selection.Copy
'select sheet (newly created sheet from above)
Sheets("" & var1).Select
' paste data
Range("A3").Select
ActiveSheet.Paste
' format data (there are many more formats below is an example)
Range("A1").Select
ActiveCell.FormulaR1C1 = "Column1"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Column2"
Columns("A:A").columnwidth = 8
Columns("B:B").columnwidth = 60
Cells.Select
Cells.EntireRow.AutoFit
Next
End Sub