Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Autofilter within a macro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Autofilter within a macro
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Autofilter within a macro
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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Autofilter within a macro
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" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofilter Macro? | Excel Discussion (Misc queries) | |||
Autofilter from macro | Excel Discussion (Misc queries) | |||
Keep autofilter after macro is run | Excel Worksheet Functions | |||
Autofilter Macro Help | Excel Discussion (Misc queries) | |||
Macro to Autofilter | Excel Programming |