sspatriots
Well-known Member
- Joined
- Nov 22, 2011
- Messages
- 585
- Office Version
- 365
- Platform
- Windows
Hi,
I've recorded a sorting macro (below). However, when I try and run it the cells that end with an "R#" move to the end of the sort. Is there a way to make this sort the table column on just the first 4 characters of the PO#? I know I can add a helper column and sort on that, but I'm afraid it may mess up another spreadsheet that references this table. Thanks, SS
Sample of my values and how they are sorting in ascending order:
I've recorded a sorting macro (below). However, when I try and run it the cells that end with an "R#" move to the end of the sort. Is there a way to make this sort the table column on just the first 4 characters of the PO#? I know I can add a helper column and sort on that, but I'm afraid it may mess up another spreadsheet that references this table. Thanks, SS
Sample of my values and how they are sorting in ascending order:
5013 |
5014 |
5015 |
5016 |
5017 |
5018 |
5019 |
5020 |
0147 R1 |
0789 R2 |
1483 R1 |
1484 R2 |
1497 R2 |
1786 R1 |
1807 R1 |
1820 R1 |
2327 R1 |
VBA Code:
Sub SortPONo()
'
' Sort PO Numbers Macro
'
'
ActiveWorkbook.Worksheets("2022").ListObjects("Table46").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("2022").ListObjects("Table46").Sort.SortFields.Add2 _
Key:=Range("Table46[PO'#]"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("2022").ListObjects("Table46").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub