The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!
Sub AddSort()
Worksheets("To Open").Sort.SortFields.Add Key:= _
Range("D15:D66"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
End Sub
Sub AddSubs()
Worksheets("To Open").Activate
Selection.Subtotal GroupBy:=5, Function:=xlAverage, TotalList:=Array(11, _
12, 13, 16, 17), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Sub Test()
'
' Test Macro
'
ActiveWorkbook.Worksheets("To Open in '13").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("To Open in '13").Sort.SortFields.Add Key:= _
Range("D15:D65"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
With ActiveWorkbook.Worksheets("To Open in '13").Sort
.SetRange Range("B14:R65")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
ActiveWorkbook.Worksheets("To Open in '13").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("To Open in '13").Sort.SortFields.Add Key:=Range("D15:D65" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("To Open in '13").Sort.SortFields.Add Key:=Range("E15:E65" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("To Open in '13").Sort.SortFields.Add Key:=Range("F15:F65" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("To Open in '13").Sort.SortFields.Add Key:=Range("J15:J65" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("To Open in '13").Sort
.SetRange Range("D15:J65")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Sub macro2()
LastRow = ActiveWorkbook.Worksheets("To Open in '13").Range("D15").End(xlDown).Row
ActiveWorkbook.Worksheets("To Open in '13").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("To Open in '13").Sort.SortFields.Add Key:=Range("D15:D" & LastRow _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("To Open in '13").Sort.SortFields.Add Key:=Range("E15:E" & LastRow _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("To Open in '13").Sort.SortFields.Add Key:=Range("F15:F" & LastRow _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("To Open in '13").Sort.SortFields.Add Key:=Range("J15:J" & LastRow _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("To Open in '13").Sort
.SetRange Range("D15:J" & LastRow)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Regards
Dim LM As Long, i As Long
LM = Range("D" & Rows.Count).End(xlUp).Row
LastRow = ActiveWorkbook.Worksheets("VSBA To Open in '13").Range("B14").End(xlDown).Row
ActiveWorkbook.Worksheets("VSBA To Open in '13").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("VSBA To Open in '13").Sort.SortFields.Add Key:=Range("D15:D" & LastRow _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("VSBA to Open in '13").Sort
.SetRange Range("B14:R" & LastRow)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
LastRow = ActiveWorkbook.Worksheets("VSBA To Open in '13").Range("D15").End(xlDown).Row
ActiveWorkbook.Worksheets("VSBA To Open in '13").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("VSBA To Open in '13").Sort.SortFields.Add Key:=Range("D15:D" & LastRow _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("VSBA To Open in '13").Sort.SortFields.Add Key:=Range("F15:F" & LastRow _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("VSBA To Open in '13").Sort.SortFields.Add Key:=Range("E15:E" & LastRow _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("VSBA To Open in '13").Sort.SortFields.Add Key:=Range("J15:J" & LastRow _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("VSBA To Open in '13").Sort
.SetRange Range("B14:R" & LastRow)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
For i = 1 To LM
If Value = "Local Market" Then
Worksheets("VSBA to Open in '13").Activate
Selection.Subtotal GroupBy:=6, Function:=xlAverage, TotalList:=Array(11, _
12, 13, 16, 17), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Else
Worksheets("VSBA to Open in '13").Activate
Selection.Subtotal GroupBy:=5, Function:=xlAverage, TotalList:=Array(11, _
12, 13, 16, 17), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End If
Next i
End Sub
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
to sort pls try ( Change the area to be sorted )
Open in new window
Regards