| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
including entire worksheet sort in a macro
I have a Macro that I went to use on an accounting report I receive weekly.
When I recorded the Macro, one of the steps was to select the entire worksheet and do a two field data sort, which works fine. Next report, which will have more or less rows, I either receive an error or it does not sort the entire worksheet. This worked fine in 2003 but in 2007 it seems to set the sort to just the number of rows present when the Macro was actually recorded. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
including entire worksheet sort in a macro
If we could see the macro, we could offer more help. Not knowing what
columns are involved and other factors makes it difficult to even begin giving you a solution. You've encountered the basic problem with a recorded macro - they record exactly what you did, down to the specific columns and rows involved with it, so any minor changes to those kind of things in the future causes unexpected results. "PatD" wrote: I have a Macro that I went to use on an accounting report I receive weekly. When I recorded the Macro, one of the steps was to select the entire worksheet and do a two field data sort, which works fine. Next report, which will have more or less rows, I either receive an error or it does not sort the entire worksheet. This worked fine in 2003 but in 2007 it seems to set the sort to just the number of rows present when the Macro was actually recorded. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
including entire worksheet sort in a macro
I get the error all the way down at
ActiveWorkbook.Worksheets("Netsales 577364").Sort.SortFields.Clear and the error says Run Time Error 9 Subscript out of Range This is the whole Macro. Sub SysApp() ' ' SysApp Macro ' ' Keyboard Shortcut: Ctrl+Shift+S ' Rows("1:5").Select Selection.Delete Shift:=xlUp Cells.Select Selection.EntireColumn.Hidden = False Range("D2").Select ActiveWindow.FreezePanes = False ActiveWindow.SmallScroll ToRight:=1 ActiveSheet.Range("$A$1:$BJ$2238").AutoFilter Field:=11, Criteria1:="EAST" ActiveWindow.ScrollColumn = 1 Columns("A:A").Select Selection.EntireColumn.Hidden = True Columns("D:F").Select Selection.EntireColumn.Hidden = True Range("J1:L1").Select Selection.EntireColumn.Hidden = True Range("O1:P1").Select Selection.EntireColumn.Hidden = True Range("Y1:Z1").Select Selection.EntireColumn.Hidden = True Range("BA1:BB1").Select Selection.EntireColumn.Hidden = True Range("BD1").Select Selection.EntireColumn.Hidden = True Range("BF1").Select Selection.EntireColumn.Hidden = True Range("B1").Select Columns("G:G").ColumnWidth = 36.83 Columns("B:B").Select Selection.Insert Shift:=xlToRight Selection.Insert Shift:=xlToRight Selection.Insert Shift:=xlToRight Columns("K:K").Select Selection.Cut Range("B1").Select ActiveSheet.Paste Columns("Q:Q").Select Selection.Cut Range("C1").Select ActiveSheet.Paste Columns("P:P").Select Selection.Cut Range("D1").Select ActiveSheet.Paste ActiveWindow.SmallScroll ToRight:=4 Range("K1").Select With ActiveWindow .SplitColumn = 5 .SplitRow = 0 End With Range("K1:U1").Select Selection.EntireColumn.Insert ActiveWindow.ScrollColumn = 12 ActiveWindow.ScrollColumn = 13 ActiveWindow.ScrollColumn = 14 ActiveWindow.ScrollColumn = 15 ActiveWindow.ScrollColumn = 16 ActiveWindow.ScrollColumn = 17 ActiveWindow.ScrollColumn = 18 ActiveWindow.ScrollColumn = 19 ActiveWindow.ScrollColumn = 20 ActiveWindow.ScrollColumn = 21 Columns("AG:AL").Select Selection.Cut ActiveWindow.SmallScroll Down:=6 ActiveWindow.ScrollColumn = 21 ActiveWindow.ScrollColumn = 20 ActiveWindow.ScrollColumn = 19 ActiveWindow.ScrollColumn = 18 ActiveWindow.ScrollColumn = 17 ActiveWindow.ScrollColumn = 16 ActiveWindow.ScrollColumn = 15 ActiveWindow.ScrollColumn = 14 ActiveWindow.ScrollColumn = 13 ActiveWindow.ScrollColumn = 12 ActiveWindow.ScrollColumn = 11 ActiveWindow.ScrollRow = 6 ActiveWindow.ScrollRow = 1 Range("K1").Select ActiveSheet.Paste ActiveWindow.ScrollColumn = 12 ActiveWindow.ScrollColumn = 13 ActiveWindow.ScrollColumn = 14 ActiveWindow.ScrollColumn = 17 ActiveWindow.ScrollColumn = 18 ActiveWindow.ScrollColumn = 20 ActiveWindow.ScrollColumn = 21 ActiveWindow.ScrollColumn = 24 Columns("AR:AU").Select Selection.Copy ActiveWindow.ScrollColumn = 31 ActiveWindow.ScrollColumn = 28 ActiveWindow.ScrollColumn = 27 ActiveWindow.ScrollColumn = 23 ActiveWindow.ScrollColumn = 22 ActiveWindow.ScrollColumn = 21 ActiveWindow.ScrollColumn = 20 ActiveWindow.ScrollColumn = 18 ActiveWindow.ScrollColumn = 17 ActiveWindow.ScrollColumn = 16 ActiveWindow.ScrollColumn = 15 ActiveWindow.ScrollColumn = 14 Range("Q1").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveWindow.ScrollColumn = 15 ActiveWindow.ScrollColumn = 16 ActiveWindow.ScrollColumn = 17 ActiveWindow.ScrollColumn = 18 ActiveWindow.ScrollColumn = 19 ActiveWindow.ScrollColumn = 20 ActiveWindow.ScrollColumn = 21 ActiveWindow.ScrollColumn = 22 ActiveWindow.ScrollColumn = 23 ActiveWindow.ScrollColumn = 24 ActiveWindow.ScrollColumn = 28 ActiveWindow.ScrollColumn = 29 ActiveWindow.ScrollColumn = 32 ActiveWindow.ScrollColumn = 33 ActiveWindow.ScrollColumn = 34 ActiveWindow.ScrollColumn = 35 ActiveWindow.ScrollColumn = 36 ActiveWindow.ScrollColumn = 37 ActiveWindow.ScrollColumn = 38 ActiveWindow.ScrollColumn = 39 ActiveWindow.ScrollColumn = 42 ActiveWindow.ScrollColumn = 43 ActiveWindow.ScrollColumn = 45 ActiveWindow.ScrollColumn = 46 ActiveWindow.ScrollColumn = 48 ActiveWindow.ScrollColumn = 49 ActiveWindow.ScrollColumn = 50 ActiveWindow.ScrollColumn = 51 ActiveWindow.ScrollColumn = 52 ActiveWindow.ScrollColumn = 53 ActiveWindow.ScrollColumn = 54 ActiveWindow.ScrollColumn = 55 ActiveWindow.ScrollColumn = 56 Columns("BN:BN").Select Selection.Cut ActiveWindow.ScrollColumn = 55 ActiveWindow.ScrollColumn = 52 ActiveWindow.ScrollColumn = 50 ActiveWindow.ScrollColumn = 45 ActiveWindow.ScrollColumn = 40 ActiveWindow.ScrollColumn = 34 ActiveWindow.ScrollColumn = 30 ActiveWindow.ScrollColumn = 28 ActiveWindow.ScrollColumn = 24 ActiveWindow.ScrollColumn = 22 ActiveWindow.ScrollColumn = 20 ActiveWindow.ScrollColumn = 19 ActiveWindow.ScrollColumn = 18 ActiveWindow.ScrollColumn = 19 ActiveWindow.ScrollColumn = 20 ActiveWindow.ScrollColumn = 21 ActiveWindow.ScrollColumn = 22 ActiveWindow.ScrollColumn = 21 ActiveWindow.ScrollColumn = 20 Range("U1").Select ActiveSheet.Paste Columns("T:T").ColumnWidth = 23.33 Columns("AF:AF").Select Selection.Cut Range("AA1").Select ActiveSheet.Paste Columns("V:V").Select Selection.EntireColumn.Hidden = True Range("AB1").Select Selection.EntireColumn.Hidden = True Range("AF1:AL1").Select Selection.EntireColumn.Hidden = True Range("AV1").Select Selection.EntireColumn.Hidden = True Range("AW1:AX1").Select Selection.EntireColumn.Hidden = True Range("BN1").Select Selection.EntireColumn.Hidden = True ActiveWindow.ScrollColumn = 53 ActiveWindow.ScrollColumn = 52 ActiveWindow.ScrollColumn = 51 ActiveWindow.ScrollColumn = 47 ActiveWindow.ScrollColumn = 46 ActiveWindow.ScrollColumn = 45 ActiveWindow.ScrollColumn = 44 ActiveWindow.ScrollColumn = 43 ActiveWindow.ScrollColumn = 42 ActiveWindow.ScrollColumn = 41 ActiveWindow.ScrollColumn = 31 ActiveWindow.ScrollColumn = 27 ActiveWindow.ScrollColumn = 23 ActiveWindow.ScrollColumn = 21 ActiveWindow.ScrollColumn = 20 ActiveWindow.ScrollColumn = 19 ActiveWindow.ScrollColumn = 18 ActiveWindow.ScrollColumn = 17 ActiveWindow.ScrollColumn = 16 ActiveSheet.Range("$A$1:$BX$2238").AutoFilter Field:=19 ActiveSheet.Range("$A$1:$BX$2238").AutoFilter Field:=19, Criteria1:=".03" _ , Operator:=xlOr, Criteria2:="<-.03" Range("B1").Select ActiveWindow.Panes(1).Activate Cells.Select Range("F1").Activate Selection.AutoFilter ActiveWorkbook.Worksheets("Netsales 577364").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Netsales 577364").Sort.SortFields.Add Key:=Range( _ "O2:O64520"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal ActiveWorkbook.Worksheets("Netsales 577364").Sort.SortFields.Add Key:=Range( _ "F2:F64520"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("Netsales 577364").Sort .SetRange Range("A1:BX64520") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub "JLatham" wrote: If we could see the macro, we could offer more help. Not knowing what columns are involved and other factors makes it difficult to even begin giving you a solution. You've encountered the basic problem with a recorded macro - they record exactly what you did, down to the specific columns and rows involved with it, so any minor changes to those kind of things in the future causes unexpected results. "PatD" wrote: I have a Macro that I went to use on an accounting report I receive weekly. When I recorded the Macro, one of the steps was to select the entire worksheet and do a two field data sort, which works fine. Next report, which will have more or less rows, I either receive an error or it does not sort the entire worksheet. This worked fine in 2003 but in 2007 it seems to set the sort to just the number of rows present when the Macro was actually recorded. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I sort a worksheet without including the column header row | Excel Discussion (Misc queries) | |||
| copying data from other worksheet including entire format | Excel Discussion (Misc queries) | |||
| How to sort without including column headings in sort | Excel Discussion (Misc queries) | |||
| why does this macro select the entire worksheet when run? | Excel Discussion (Misc queries) | |||
| data sort is not including all columns in sort | Excel Discussion (Misc queries) | |||
Linear Mode
