Hi all,
I've provided below a simplified version of the worksheets I'm trying to sort and the Macro I've recorded to sort the worksheet, however, I need to edit/update the macro for use on all the worksheets in the workbooks I'm sorting and specifically to REFINE the sort criteria for Column D and Column E.
The sort is run simultaneously and runs hierarchically A,B,D,E,F,G.
In column D I'd like the macro to sort numerically as it is doing but to IGNORE the prefix c. before the year when it occurs in the column.
In column E I'd like the macro to sort alphabetically as it is doing but to IGNORE any inverted commas: ' ' around the text as these are messing up the alphabetical sort for this column.
<tbody>
</tbody>
Below is the recorded Macro which I've called MANUAL_SORT2 this was recorded on a worksheet called BBCO with 103 rows in the column. I like the macro to be usable on all worksheets in the workbook also to add the worksheets all have varying numbers of rows!
Sub MANUAL_SORT2()
'
' MANUAL_SORT2 Macro
'
'
ActiveWorkbook.Worksheets("BBCO").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("BBCO").Sort.SortFields.Add Key:=Range("A2:A103"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("BBCO").Sort.SortFields.Add Key:=Range("B2:B103"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("BBCO").Sort.SortFields.Add Key:=Range("C2:C103"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("BBCO").Sort.SortFields.Add Key:=Range("D2:D103"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("BBCO").Sort.SortFields.Add Key:=Range("E2:E103"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("BBCO").Sort.SortFields.Add Key:=Range("F2:F103"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("BBCO").Sort.SortFields.Add Key:=Range("G2:G103"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("BBCO").Sort
.SetRange Range("A1:AB103")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Thanks in advance if any of you can help!
I've provided below a simplified version of the worksheets I'm trying to sort and the Macro I've recorded to sort the worksheet, however, I need to edit/update the macro for use on all the worksheets in the workbooks I'm sorting and specifically to REFINE the sort criteria for Column D and Column E.
The sort is run simultaneously and runs hierarchically A,B,D,E,F,G.
In column D I'd like the macro to sort numerically as it is doing but to IGNORE the prefix c. before the year when it occurs in the column.
In column E I'd like the macro to sort alphabetically as it is doing but to IGNORE any inverted commas: ' ' around the text as these are messing up the alphabetical sort for this column.
A | B | C | D | E | F | G |
Patrick | Ann | (possibly) | 1910 | Apple on Table | ART10020 | WMRII_BBCO_1771.jpg |
Jones | Simon | Woman in Blue | ART10013 | WMRII_BBCO_1022.jpg | ||
Scott | Peter | (after) | 1920 | Forest Glade | ART10024 | WMRII_BBCO_1220.jpg |
Scott | Peter | (attributed) | c.1919 | 'Still Life with Bottle' | ART10030 | WMRII_BBCO_1300.jpg |
Scott | Peter | (circle of) | 1930 | Snow-scene with Larches | ART10015 | WMRII_BBCO_13.jpg |
Scott | Peter | (school of) | 1937 | 'Shrimp Seller' | ART10013 | WMRII_BBCO_1322.jpg |
Scott | Peter | 1910 | ART10022 | WMRII_BBCO_1344.jpg | ||
Purvis | Jack | Untitled | ART10085 | WMRII_BBCO_1399.jpg | ||
Ashley | Sheila | Mediterranean View | ART10076 | WMRII_BBCO_1000.jpg | ||
Ashley | Sheila | 'Landscape' | ART10092 | WMRII_BBCO_1348.jpg |
<tbody>
</tbody>
Below is the recorded Macro which I've called MANUAL_SORT2 this was recorded on a worksheet called BBCO with 103 rows in the column. I like the macro to be usable on all worksheets in the workbook also to add the worksheets all have varying numbers of rows!
Sub MANUAL_SORT2()
'
' MANUAL_SORT2 Macro
'
'
ActiveWorkbook.Worksheets("BBCO").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("BBCO").Sort.SortFields.Add Key:=Range("A2:A103"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("BBCO").Sort.SortFields.Add Key:=Range("B2:B103"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("BBCO").Sort.SortFields.Add Key:=Range("C2:C103"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("BBCO").Sort.SortFields.Add Key:=Range("D2:D103"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("BBCO").Sort.SortFields.Add Key:=Range("E2:E103"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("BBCO").Sort.SortFields.Add Key:=Range("F2:F103"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("BBCO").Sort.SortFields.Add Key:=Range("G2:G103"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("BBCO").Sort
.SetRange Range("A1:AB103")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Thanks in advance if any of you can help!