Edit/Expansion of Sort macro needed

[JONPM]

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.

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!

 

[JONPM]

I've managed to edit the macro to this so that it works on all worksheets with columns of varying length ...... just now how to refine the search criteria...

Sub MANUAL_SORT2()
'
' MANUAL_SORT2 Macro
'


'
LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range("A2:A" & LR), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveSheet.Sort.SortFields.Add Key:=Range("B2:B" & LR), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveSheet.Sort.SortFields.Add Key:=Range("C2:C" & LR), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveSheet.Sort.SortFields.Add Key:=Range("D2:D" & LR), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveSheet.Sort.SortFields.Add Key:=Range("E2:E" & LR), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveSheet.Sort.SortFields.Add Key:=Range("F2:F" & LR), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveSheet.Sort.SortFields.Add Key:=Range("G2:G" & LR), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range("A1:G" & LR)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub