Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
works and doesn't work
used auto filter to build a macro works fine then copied it over and edited
for next sheet 8 times works all except 2 sheets. can see no diff except edit I did. Here is one that works and one that doesn't five works six doesn't six produces header no data. modules 1-5 work fine 6 out 7ok 8 out 9 ok all copied from same. Hoping someone can see what is wrong. Can start over and build 6&8 over if needed. For my knowledge would like to know what is wrong. Thanks to All Sub Five() ' Two Macro ' Macro recorded 9/28/2007 by Curtiss A. Greer Range("D4").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="5 Float" Range("A1:N104").Select Selection.Copy Sheets("5").Select Range("A1").Select ActiveSheet.Paste Columns("E:L").Select Columns("A:A").ColumnWidth = 3.57 Columns("B:B").ColumnWidth = 11.29 Columns("C:C").ColumnWidth = 22.43 Columns("D:D").ColumnWidth = 15.14 Application.CutCopyMode = False Selection.ClearContents Range("G14").Select Sheets("Data").Select Range("E20").Select Selection.AutoFilter End Sub Sub Six() ' Two Macro ' Macro recorded 9/28/2007 by Curtiss A. Greer Range("D4").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="6 March Mil" Range("A1:N104").Select Selection.Copy Sheets("6").Select Range("A1").Select ActiveSheet.Paste Columns("E:L").Select Columns("A:A").ColumnWidth = 3.57 Columns("B:B").ColumnWidth = 11.29 Columns("C:C").ColumnWidth = 22.43 Columns("D:D").ColumnWidth = 15.14 Application.CutCopyMode = False Selection.ClearContents Range("G14").Select Sheets("Data").Select Range("E20").Select Selection.AutoFilter End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
works and doesn't work
Since the two procedures appear to be identical except for the selection
criteria, the problem must lie in the criteria versus the data base. That is, it is not finding the criteria you have specified. You can walk through the procedure one step at a time to see if it is doing what you expect and identify the step where it does not do what you expect. Then you can more easily analyze why it is not doing it. Below is a cleaned up version of your code, that removes all the unnecessary "selects". I did not test it so if you decide to use it, you should first test it on copies of your files. Sub Five() ' Two Macro ' Macro recorded 9/28/2007 by Curtiss A. Greer Range("D4").AutoFilter Field:=1, Criteria1:="5 Float" Range("A1:N104").Copy Sheets("5").Range("A1") Columns("A:A").ColumnWidth = 3.57 Columns("B:B").ColumnWidth = 11.29 Columns("C:C").ColumnWidth = 22.43 Columns("D:D").ColumnWidth = 15.14 Application.CutCopyMode = False Columns("E:L").ClearContents Range("G14").Select Sheets("Data").Range("E20").AutoFilter End Sub Sub Six() ' Two Macro ' Macro recorded 9/28/2007 by Curtiss A. Greer Range("D4").AutoFilter Field:=1, Criteria1:="6 March Mil" Range("A1:N104").Copy Sheets("6").Range("A1") Columns("A:A").ColumnWidth = 3.57 Columns("B:B").ColumnWidth = 11.29 Columns("C:C").ColumnWidth = 22.43 Columns("D:D").ColumnWidth = 15.14 Application.CutCopyMode = False Columns("E:L").ClearContents Range("G14").Select Sheets("Data").Range("E20").AutoFilter End Sub "Curt" wrote: used auto filter to build a macro works fine then copied it over and edited for next sheet 8 times works all except 2 sheets. can see no diff except edit I did. Here is one that works and one that doesn't five works six doesn't six produces header no data. modules 1-5 work fine 6 out 7ok 8 out 9 ok all copied from same. Hoping someone can see what is wrong. Can start over and build 6&8 over if needed. For my knowledge would like to know what is wrong. Thanks to All Sub Five() ' Two Macro ' Macro recorded 9/28/2007 by Curtiss A. Greer Range("D4").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="5 Float" Range("A1:N104").Select Selection.Copy Sheets("5").Select Range("A1").Select ActiveSheet.Paste Columns("E:L").Select Columns("A:A").ColumnWidth = 3.57 Columns("B:B").ColumnWidth = 11.29 Columns("C:C").ColumnWidth = 22.43 Columns("D:D").ColumnWidth = 15.14 Application.CutCopyMode = False Selection.ClearContents Range("G14").Select Sheets("Data").Select Range("E20").Select Selection.AutoFilter End Sub Sub Six() ' Two Macro ' Macro recorded 9/28/2007 by Curtiss A. Greer Range("D4").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="6 March Mil" Range("A1:N104").Select Selection.Copy Sheets("6").Select Range("A1").Select ActiveSheet.Paste Columns("E:L").Select Columns("A:A").ColumnWidth = 3.57 Columns("B:B").ColumnWidth = 11.29 Columns("C:C").ColumnWidth = 22.43 Columns("D:D").ColumnWidth = 15.14 Application.CutCopyMode = False Selection.ClearContents Range("G14").Select Sheets("Data").Select Range("E20").Select Selection.AutoFilter End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
works and doesn't work
I did find the problem. I went back to database and copied and pasted the
"criteria" I checked for spaces etc and when pasteing it all worked. Thanks for the clean up. Your assistance greatly appreciated. "JLGWhiz" wrote: Since the two procedures appear to be identical except for the selection criteria, the problem must lie in the criteria versus the data base. That is, it is not finding the criteria you have specified. You can walk through the procedure one step at a time to see if it is doing what you expect and identify the step where it does not do what you expect. Then you can more easily analyze why it is not doing it. Below is a cleaned up version of your code, that removes all the unnecessary "selects". I did not test it so if you decide to use it, you should first test it on copies of your files. Sub Five() ' Two Macro ' Macro recorded 9/28/2007 by Curtiss A. Greer Range("D4").AutoFilter Field:=1, Criteria1:="5 Float" Range("A1:N104").Copy Sheets("5").Range("A1") Columns("A:A").ColumnWidth = 3.57 Columns("B:B").ColumnWidth = 11.29 Columns("C:C").ColumnWidth = 22.43 Columns("D:D").ColumnWidth = 15.14 Application.CutCopyMode = False Columns("E:L").ClearContents Range("G14").Select Sheets("Data").Range("E20").AutoFilter End Sub Sub Six() ' Two Macro ' Macro recorded 9/28/2007 by Curtiss A. Greer Range("D4").AutoFilter Field:=1, Criteria1:="6 March Mil" Range("A1:N104").Copy Sheets("6").Range("A1") Columns("A:A").ColumnWidth = 3.57 Columns("B:B").ColumnWidth = 11.29 Columns("C:C").ColumnWidth = 22.43 Columns("D:D").ColumnWidth = 15.14 Application.CutCopyMode = False Columns("E:L").ClearContents Range("G14").Select Sheets("Data").Range("E20").AutoFilter End Sub "Curt" wrote: used auto filter to build a macro works fine then copied it over and edited for next sheet 8 times works all except 2 sheets. can see no diff except edit I did. Here is one that works and one that doesn't five works six doesn't six produces header no data. modules 1-5 work fine 6 out 7ok 8 out 9 ok all copied from same. Hoping someone can see what is wrong. Can start over and build 6&8 over if needed. For my knowledge would like to know what is wrong. Thanks to All Sub Five() ' Two Macro ' Macro recorded 9/28/2007 by Curtiss A. Greer Range("D4").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="5 Float" Range("A1:N104").Select Selection.Copy Sheets("5").Select Range("A1").Select ActiveSheet.Paste Columns("E:L").Select Columns("A:A").ColumnWidth = 3.57 Columns("B:B").ColumnWidth = 11.29 Columns("C:C").ColumnWidth = 22.43 Columns("D:D").ColumnWidth = 15.14 Application.CutCopyMode = False Selection.ClearContents Range("G14").Select Sheets("Data").Select Range("E20").Select Selection.AutoFilter End Sub Sub Six() ' Two Macro ' Macro recorded 9/28/2007 by Curtiss A. Greer Range("D4").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="6 March Mil" Range("A1:N104").Select Selection.Copy Sheets("6").Select Range("A1").Select ActiveSheet.Paste Columns("E:L").Select Columns("A:A").ColumnWidth = 3.57 Columns("B:B").ColumnWidth = 11.29 Columns("C:C").ColumnWidth = 22.43 Columns("D:D").ColumnWidth = 15.14 Application.CutCopyMode = False Selection.ClearContents Range("G14").Select Sheets("Data").Select Range("E20").Select Selection.AutoFilter End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hyperlink formula works, and then doesn't work | Excel Discussion (Misc queries) | |||
Code not work Take same action on all wkshts or selected works | Excel Programming | |||
macro doesn't work on Excel for the mac but works on Windows XL?? | Excel Programming | |||
it works, it doesn work, its works....and so on. | Excel Programming | |||
How can I get EXCEL formulas to work in Works? | Excel Worksheet Functions |