Using Autofilter within a macro

  • Thread starter Thread starter standard_guy
  • Start date Start date
S

standard_guy

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.
 
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
 
Thanks, the simplification part worked but I still have the same problem. I
run the bigger macro, it stopped, I stop the run and end up on the sheet.
Then I start the little macro and it works if I start from the sheet it is
on, whether it is yours or mine. I must have left something in limbo in the
bigger macro that is cleared by stopping it. I will use that better way of
writing out the aurofilter directions.
--
Rick H


Joel said:
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 said:
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.
Click to expand...
Click to expand...
 
Since I get a lot out of reading other people's questions and answers I
figured I'd come back an post the solution when I found it in case this can
help someone in the future. Here's the cut and paste from the help file:

_______
A method or property can't be used because of security settings. For
example, the properties and methods of the VBE object for manipulating the
Microsoft Visual Basic for Applications (VBA) code stored in an Microsoft
Office document are inaccessible by default.
To turn on trusted access to Visual Basic Projects:

On the Tools menu, point to Macro, and then click Security.
On the Trusted Sources tab, select the Trust access to Visual Basic Project
check box.
For more information about how to use the method, search for the method name
in Visual Basic Help.
___________

I'm not sure I how I haven't stumbled upon that before but there it is in
case it bites anybody else. Have a good day.
--
Rick H


standard_guy said:
Thanks, the simplification part worked but I still have the same problem. I
run the bigger macro, it stopped, I stop the run and end up on the sheet.
Then I start the little macro and it works if I start from the sheet it is
on, whether it is yours or mine. I must have left something in limbo in the
bigger macro that is cleared by stopping it. I will use that better way of
writing out the aurofilter directions.
--
Rick H


Joel said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top