Using Autofilter within a macro

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

Joel

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
 
S

standard_guy

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

standard_guy

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help me5 1
Different Results from the Same Macro 3
Help me4 2
VBA Filter 2
works in 2007 but not in 2010 0
Macro Loop 0
Cleanup this macro please 3
Help merging two VBA codes 2

Top