Feb 06 2019 07:52 AM
Feb 06 2019 07:52 AM
Hello, when I record a macro in Excel that has a sequence of sorts, the sorts don't hold. For example, I sort column A, then sort column B. The column A sort is lost as soon as I sort column B.
I don't know Visual Basic. I have previously easily created and run such simple macros. But today macros aren't working for me.
Feb 06 2019 08:39 AM
Feb 06 2019 09:05 AM
Jan, thanks for your help. Below is the code of a short test that:
1. Filters on two values of Column BR
2. Sorts ascending Column BR
3. Sorts ascending Column BV.
As soon as I sort Column BV as I record the macros, I lose the sort in Column BR. No blank rows or blank columns in the spreadsheet
Sub PrincipalGiftTiers1_2()
'
' PrincipalGiftTiers1_2 Macro
' segmentation protocol: filter tiers 1:2. Sort Tiers, Donor Segment, WealthPoint Rating, Loyal Donor, Last Gift Date, Cumulative
'
'
ActiveSheet.Range("$A$1:$CA$578").AutoFilter Field:=70, Criteria1:= _
"=Tier 1: $500K+", Operator:=xlOr, Criteria2:="=Tier 2: $250K-$500K"
ActiveWorkbook.Worksheets("Rhode Island PG Tiers and Loyal").AutoFilter.Sort. _
SortFields.Clear
ActiveWorkbook.Worksheets("Rhode Island PG Tiers and Loyal").AutoFilter.Sort. _
SortFields.Add2 Key:=Range("BR1:BR578"), SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Rhode Island PG Tiers and Loyal").AutoFilter. _
Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWorkbook.Worksheets("Rhode Island PG Tiers and Loyal").AutoFilter.Sort. _
SortFields.Clear
ActiveWorkbook.Worksheets("Rhode Island PG Tiers and Loyal").AutoFilter.Sort. _
SortFields.Add2 Key:=Range("BV1:BV578"), SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Rhode Island PG Tiers and Loyal").AutoFilter. _
Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Feb 07 2019 12:59 AM
I think you should have recorded the sort by clicking (Home tab) Sort & FIlter, Custom sort and then setting up the two sorts in the custom sort dialog. That would have resulted in code like below:
Sub PrincipalGiftTiers1_2() ' ' PrincipalGiftTiers1_2 Macro ' segmentation protocol: filter tiers 1:2. Sort Tiers, Donor Segment, WealthPoint Rating, Loyal Donor, Last Gift Date, Cumulative ' ' ActiveSheet.Range("$A$1:$CA$578").AutoFilter Field:=70, Criteria1:= _ "=Tier 1: $500K+", Operator:=xlOr, Criteria2:="=Tier 2: $250K-$500K" ActiveWorkbook.Worksheets("Rhode Island PG Tiers and Loyal").AutoFilter.Sort. _ SortFields.Clear ActiveWorkbook.Worksheets("Rhode Island PG Tiers and Loyal").AutoFilter.Sort. _ SortFields.Add2 Key:=Range("BR1:BR578"), SortOn:=xlSortOnValues, Order:= _ xlAscending, DataOption:=xlSortNormal ActiveWorkbook.Worksheets("Rhode Island PG Tiers and Loyal").AutoFilter.Sort. _ SortFields.Add2 Key:=Range("BV1:BV578"), SortOn:=xlSortOnValues, Order:= _ xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Rhode Island PG Tiers and Loyal").AutoFilter. _ Sort .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub
Feb 07 2019 03:13 AM
Jan,
Yes! Thank you! For some reason, I had it in my head that recording the macro allowed me to skip having to do the custom sort. Regards, Randy