JamesUKnow
New Member
- Joined
- Feb 8, 2014
- Messages
- 5
Hi There,
I need to perform a series of filters/sorts on a data table of sales opportunities.
I have recorded a macro and attached to a function button which works well from a results perspective but is very slow.
Not sure if this is something to do with my laptop, the macro of the data set.
The data table is 1000 lines long, currently only populated with c.100 entries and 50 columns wide, most of which are blank or filled with "---".
The macor is as follows:
Sub Filter_Expected()
'
' Filter_Expected Macro
'
'
Rows("8:8").Select
Selection.AutoFilter
Selection.AutoFilter
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 5
ActiveWorkbook.Worksheets("New Template").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("New Template").AutoFilter.Sort.SortFields.Add Key _
:=Range("A8"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("New Template").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveSheet.Range("$A$8:$BH$1008").AutoFilter Field:=4, Criteria1:=Array( _
"Open", "Pending", "="), Operator:=xlFilterValues
ActiveSheet.Range("$A$8:$BH$1008").AutoFilter Field:=5, Criteria1:= _
"=Expected", Operator:=xlOr, Criteria2:="="
ActiveSheet.Range("$A$8:$BH$1008").AutoFilter Field:=3, Criteria1:="<>"
ActiveWorkbook.Worksheets("New Template").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("New Template").AutoFilter.Sort.SortFields.Add Key _
:=Range("C8:C1008"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("New Template").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Is there something wrong with the macro?
Would this be better done with VBA (of which I have virtually no experience.
Your advice would be appreciated.
Regards, James
I need to perform a series of filters/sorts on a data table of sales opportunities.
I have recorded a macro and attached to a function button which works well from a results perspective but is very slow.
Not sure if this is something to do with my laptop, the macro of the data set.
The data table is 1000 lines long, currently only populated with c.100 entries and 50 columns wide, most of which are blank or filled with "---".
The macor is as follows:
Sub Filter_Expected()
'
' Filter_Expected Macro
'
'
Rows("8:8").Select
Selection.AutoFilter
Selection.AutoFilter
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 5
ActiveWorkbook.Worksheets("New Template").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("New Template").AutoFilter.Sort.SortFields.Add Key _
:=Range("A8"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("New Template").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveSheet.Range("$A$8:$BH$1008").AutoFilter Field:=4, Criteria1:=Array( _
"Open", "Pending", "="), Operator:=xlFilterValues
ActiveSheet.Range("$A$8:$BH$1008").AutoFilter Field:=5, Criteria1:= _
"=Expected", Operator:=xlOr, Criteria2:="="
ActiveSheet.Range("$A$8:$BH$1008").AutoFilter Field:=3, Criteria1:="<>"
ActiveWorkbook.Worksheets("New Template").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("New Template").AutoFilter.Sort.SortFields.Add Key _
:=Range("C8:C1008"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("New Template").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Is there something wrong with the macro?
Would this be better done with VBA (of which I have virtually no experience.
Your advice would be appreciated.
Regards, James