including entire worksheet sort in a macro

[PatD]

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.

 

[JLatham]

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]

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("O11").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").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