| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
filters and macros
Hello,
I do have a spreadsheet, where I do have created auto filter. When I manually set the custom autofilter logic, the correct data will be filtered. Now I recorded a macro, which sets the autofilter, copys some subtotal results and then sets some other filters, where it does the same. ============= Macro ============= Selection.AutoFilter Field:=13, Criteria1:="0,5", Operator:=xlAnd, _ Criteria2:="<=1" Range("T1").Select Application.CutCopyMode = False Selection.Copy Range("AP2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("U1").Select Application.CutCopyMode = False Selection.Copy Range("AP3").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ================== When I now run the filter, no rows will be selected. If I go into debug mode and set a breck point after the first cut function, I can see that the filter was set by the macro, but not activated. Any idear what when wrong in the recording? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
filters and macros
Hi Martin,
I ran your macro on data designed to mimic your autofilter range and, once I had changed the first criterion from 0,5 to 0.5 (comma === dot/drop) to accord with my local settings, it filtered my data as expected. --- Regards, Norman "Martin" wrote in message ... Hello, I do have a spreadsheet, where I do have created auto filter. When I manually set the custom autofilter logic, the correct data will be filtered. Now I recorded a macro, which sets the autofilter, copys some subtotal results and then sets some other filters, where it does the same. ============= Macro ============= Selection.AutoFilter Field:=13, Criteria1:="0,5", Operator:=xlAnd, _ Criteria2:="<=1" Range("T1").Select Application.CutCopyMode = False Selection.Copy Range("AP2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("U1").Select Application.CutCopyMode = False Selection.Copy Range("AP3").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ================== When I now run the filter, no rows will be selected. If I go into debug mode and set a breck point after the first cut function, I can see that the filter was set by the macro, but not activated. Any idear what when wrong in the recording? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
filters and macros
Hi Martin,
(comma === dot/drop) was intended as: (comma === dot / stop) --- Regards, Norman "Norman Jones" wrote in message ... Hi Martin, I ran your macro on data designed to mimic your autofilter range and, once I had changed the first criterion from 0,5 to 0.5 (comma === dot/drop) to accord with my local settings, it filtered my data as expected. --- Regards, Norman "Martin" wrote in message ... Hello, I do have a spreadsheet, where I do have created auto filter. When I manually set the custom autofilter logic, the correct data will be filtered. Now I recorded a macro, which sets the autofilter, copys some subtotal results and then sets some other filters, where it does the same. ============= Macro ============= Selection.AutoFilter Field:=13, Criteria1:="0,5", Operator:=xlAnd, _ Criteria2:="<=1" Range("T1").Select Application.CutCopyMode = False Selection.Copy Range("AP2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("U1").Select Application.CutCopyMode = False Selection.Copy Range("AP3").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ================== When I now run the filter, no rows will be selected. If I go into debug mode and set a breck point after the first cut function, I can see that the filter was set by the macro, but not activated. Any idear what when wrong in the recording? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
filters and macros
Norman,
even so my local number setting uses commas, I have changed the macro to dot and now it works fine. Thanks Martin "Norman Jones" wrote: Hi Martin, I ran your macro on data designed to mimic your autofilter range and, once I had changed the first criterion from 0,5 to 0.5 (comma === dot/drop) to accord with my local settings, it filtered my data as expected. --- Regards, Norman "Martin" wrote in message ... Hello, I do have a spreadsheet, where I do have created auto filter. When I manually set the custom autofilter logic, the correct data will be filtered. Now I recorded a macro, which sets the autofilter, copys some subtotal results and then sets some other filters, where it does the same. ============= Macro ============= Selection.AutoFilter Field:=13, Criteria1:="0,5", Operator:=xlAnd, _ Criteria2:="<=1" Range("T1").Select Application.CutCopyMode = False Selection.Copy Range("AP2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("U1").Select Application.CutCopyMode = False Selection.Copy Range("AP3").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ================== When I now run the filter, no rows will be selected. If I go into debug mode and set a breck point after the first cut function, I can see that the filter was set by the macro, but not activated. Any idear what when wrong in the recording? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
filters and macros
Hi Martin,
even so my local number setting uses commas, I have changed the macro to dot and now it works fine. Of course, VBA always uses US settings but translate the dot / stop to a comma to accord with the user's local settings. Something I knew and (often) forget! --- Regards, Norman "Martin" wrote in message ... Norman, even so my local number setting uses commas, I have changed the macro to dot and now it works fine. Thanks Martin "Norman Jones" wrote: Hi Martin, I ran your macro on data designed to mimic your autofilter range and, once I had changed the first criterion from 0,5 to 0.5 (comma === dot/drop) to accord with my local settings, it filtered my data as expected. --- Regards, Norman "Martin" wrote in message ... Hello, I do have a spreadsheet, where I do have created auto filter. When I manually set the custom autofilter logic, the correct data will be filtered. Now I recorded a macro, which sets the autofilter, copys some subtotal results and then sets some other filters, where it does the same. ============= Macro ============= Selection.AutoFilter Field:=13, Criteria1:="0,5", Operator:=xlAnd, _ Criteria2:="<=1" Range("T1").Select Application.CutCopyMode = False Selection.Copy Range("AP2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("U1").Select Application.CutCopyMode = False Selection.Copy Range("AP3").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ================== When I now run the filter, no rows will be selected. If I go into debug mode and set a breck point after the first cut function, I can see that the filter was set by the macro, but not activated. Any idear what when wrong in the recording? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Pivot Table filters, especially DATE filters | Excel Worksheet Functions | |||
| Using Filters in Macros | Excel Discussion (Misc queries) | |||
| Using Filters in Macros | Excel Discussion (Misc queries) | |||
| Filters, Subtotal & Intacted Results after the filters' Removal | Excel Discussion (Misc queries) | |||
| macros, filters, HELP | Excel Programming | |||
Linear Mode
