You need to specify the sheet name with the autofilter criteria. I also made
some changes to make the code simplier.


Sub FilterandCalcLoads()
'
with sheets("Sheet1")
.Range("a9:z20000").AutoFilter Field:=24, Criteria1:="=1", Operator:=xlAnd
.Range("Z9").FormulaR1C1 = "=(RC[-11]-R[-2]C[-11])"
.Range("Z9").Copy _
destination:= .Range("Z13:Z20000")
end with
'
End Sub



"standard_guy" wrote:

Hi,

This macro works for what I want to do:

Sub FilterandCalcLoads()
'
Range("a9:z20000").Select
Selection.AutoFilter Field:=24, Criteria1:="=1", Operator:=xlAnd
Range("A9:X20000").Select
Range("Z9").Select
ActiveCell.FormulaR1C1 = "=(RC[-11]-R[-2]C[-11])"
Range("Z9").Select
Selection.Copy
Range("Z13:Z20000").Select
Range("Z13:Z20000").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
'
End Sub

However, when I start it at the end of this macro:

Sub DeliveryHistory()
'
Range("X12").Select
ActiveCell.FormulaR1C1 = "=(RC[-14]-R[-2]C[-14])*24"
Range("X12").Select
Selection.Copy
Range("X13:X20000").Select
Range("X13:X20000").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Calculate
Sheets.Add
Sheets("Sheet1").Select
Rows("1:9").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("A1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "x"
Range("A1").Select
Selection.Copy
Range("A2:A20000").Select
Range("A2:a20000").Activate
ActiveSheet.Paste
Application.Run "FilterandCalcLoads"
End Sub

I get an error message "AutoFilter method of range class failed" at the
autofilter line.

If I run the first macro, get the error, close out of VB editor, then run
the second macro by itself, it works. I have tried a couple of things,
didn't work, figure it is something easy that someone here can answer in a
few seconds. Thanks.
--
Rick H