VBA to Sort Multiple Sheets in Excel Workbook with different criteria

[lynnydd76]

Hi All,

I'm trying to get a macro to perform the sort function across multiple sheets to reduce click count.

The columns and header rows don't change but when I record the macro, it never works on the next workbook.

I want to run it from copies of the same workbook so it needs to point to the active workbook.

This is what I need it to do;

Sheet 1 (Contacts):
Sort range A3:A1000 by columns A, then B (all ascending)

Sheet 2 (Centres):
Sort range A8:A1000 by columns B, then C, then A (all ascending)

Sheet 3 (Cases):
Sort range A4:A1000 by columns A, then B (all ascending)

Sheet 4 (Requests):
Sort range A4:A1000 by columns A, then B, then G (all ascending)

Any help would be appreciated, I'm OK at amending recorded macros with the basics but not writing from scratch.

 

[JLGWhiz]

See if this will work

Sub Sorter()
Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet, sh4 As Worksheet
Set sh1 = Sheets("Contacts")
Set sh2 = Sheets("Centres")
Set sh3 = Sheets("Cases")
Set sh4 = Sheets("Requests")
sh1.Range("A3:B1000").Sort sh1.Range("A3"), xlAscending, sh1.Range("B3"), , xlAscending
sh2.Range("A8:C1000").Sort sh2.Range("B8"), xlAscending, sh2.Range("C8"), , xlAscending, sh2.Range("A8"), xlAscending
sh3.Range("A4:B1000").Sort sh3.Range("A4").xlAscending, sh3.Range("B4"), , xlAscending
sh4.Range("A4:C1000").Sort sh4.Range("A4").xlAscending, sh4.Range("B4"), , xlAscending, sh4.Range("C4"), xlAscending
End Sub

This will run in the same workbook that the sheets are in. It will not work to run it from a single workbook for sheets in different workbooks.

 

[lynnydd76]

Thanks JLGWhiz, I can't get that to work.

The commas (, , vs , ) and dots (.xlascending vs xlascending) are inconsistent across the last section. Which way should they be?

Cheers, Lynette