ExcelBanter - AutoFilter Criteria in VBA

Hi Don,

Thanks so much for your response. I will definitely that. In the meanwhile,
I rewrote the code as below and it seems to be working for the time being.
Thanks for all the help!

Application.ScreenUpdating = False
Dim engcode As Range, product As Range, quotetype As Range, a As Range
Set engcode = ThisWorkbook.Worksheets("a").Range("e7")
Set product = ThisWorkbook.Worksheets("a").Range("e8")
Set quotetype = ThisWorkbook.Worksheets("a").Range("e9")
Set a = ThisWorkbook.Worksheets("Raw").Range("a2")
ThisWorkbook.Worksheets("Raw").AutoFilterMode = False
If engcode = "ALL" Then
a.AutoFilter Field:=1, Criteria1:=product
a.AutoFilter Field:=3, Criteria1:=quotetype
a.AutoFilter Field:=7, Criteria1:="Jan"
Worksheets("Raw").Range("A1").FormulaR1C1 =
"=SUBTOTAL(102,R[1]C[12]:R[10000]C[12])"
Worksheets("a").Range("f14").Value =
Worksheets("Raw").Range("A1").Value
Worksheets("Raw").Range("B1").FormulaR1C1 =
"=SUBTOTAL(109,R[1]C[7]:R[10000]C[7])"
Worksheets("a").Range("f15").Value =
Worksheets("Raw").Range("B1").Value
Sheets("a").Select
Else
a.AutoFilter Field:=12, Criteria1:=engcode
a.AutoFilter Field:=1, Criteria1:=product
a.AutoFilter Field:=3, Criteria1:=quotetype
a.AutoFilter Field:=7, Criteria1:="Jan"
Worksheets("Raw").Range("A1").FormulaR1C1 =
"=SUBTOTAL(102,R[1]C[12]:R[10000]C[12])"
Worksheets("a").Range("f14").Value = Worksheets("Raw").Range("A1").Value
Worksheets("Raw").Range("B1").FormulaR1C1 =
"=SUBTOTAL(109,R[1]C[7]:R[10000]C[7])"
Worksheets("a").Range("f15").Value =
Worksheets("Raw").Range("B1").Value
Sheets("a").Select
End If
End Sub
--
Regards,
Abhi

"Don Guillett" wrote:

try this idea. Notice the DOT ( . ) placement within the WITH statements.
Can be fired from anywhere in the workbook.
Sub filterit()
With Sheets("a")
engcode = .Range("a1")
'.etc
'.etc
'.etc
End With

With Sheets("raw")
With .Range("a6:d6") 'yourtoprowofrange
.AutoFilter
.AutoFilter Field:=1, Criteria1:=engcode
'.etc
'.etc

..Range("A1").FormulaR1C1 = "=SUBTOTAL(102,R[1]C:RC)"
Sheets("a").Range("a1").Value = .Range("a1")
' MsgBox "done"
.AutoFilter
End With
End With

End Sub

--
Don Guillett
SalesAid Software

"Abhi" wrote in message
...
I am not able to set the criteria in a autofilter to be dynamic (i.e as the
user is selecting it.) in VBA. The code is as below:

Code:

Sub filt2()
'
' filt2 Macro
' Macro recorded 4/23/2007 by Abhijit
'

'
Dim engcode As Range, product As Range, quotetype As Range
Set engcode = ThisWorkbook.Worksheets("a").Range("a1")
Set product = ThisWorkbook.Worksheets("a").Range("a2")
Set quotetype = ThisWorkbook.Worksheets("a").Range("a3")
Sheets("Raw").Select
Selection.AutoFilter
Selection.AutoFilter
Selection.AutoFilter Field:=13, Criteria1:=engcode.Value
Selection.AutoFilter Field:=2, Criteria1:=product.Value
Selection.AutoFilter Field:=4, Criteria1:=quotetype.Value
Selection.AutoFilter Field:=8, Criteria1:="Jan"
Sheets("Raw").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(102,R[1]C:RC)"
Range("A1").Select
Selection.Copy
Sheets("a").Select
Range("F14").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.ClearContents
Sheets("Raw").Select
Selection.Copy
Sheets("a").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

Any suggestions.

Please help

--
Regards,
Abhi