Macro to Loop through and sort worksheets

[PVANS]

Good morning

I have a workbook with about 30 worksheets that need to be sorted by two
columns, date and currency.

I recorded a macro to sort a single worksheet (after discovering you cannot
sort multiple worksheets at once), and then went online to try and find a
macro code to loop through worksheets. I managed to find the following:

Sub SortData()

' SortData Macro

Dim ws As Worksheet
Select Case ws.Name
Case "Percentages", "MasterNonDMA%", "MasterNonDMA", "MasterDMA%",
"MasterDMA", "Reciept Saxo", "Model Account"
Exit Sub 'do nothing in fact
Case Else
'THIS IS WHERE MY RECORDED CODE STARTS
Cells.Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("H2") _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=1,
MatchCase:=False _
, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2:= _
xlSortNormal
'your sorting code
'THIS IS WHERE MY RECORDED CODE FINISHES
End Select


End Sub

However, when I attempt to run the code, I recieve the error:
Runtime error '91':
Object variable or With Block variable not set.

If I debug the problem, it highlights the following line:
Select Case ws.Name

I am sure I have made a silly error, or overlooked something relatively
simple, but I am completely stuck. Could someone please assist me?

Thank you

Regards

 

[Jacob Skaria]

Try the below

Sub SortData()
Dim ws As Worksheet

For Each ws In Worksheets
Select Case ws.Name
Case "Percentages", "MasterNonDMA%", "MasterNonDMA", _
"MasterDMA%", "MasterDMA", "Reciept Saxo", "Model Account"
Exit Sub 'do nothing in fact
Case Else
ws.Cells.Sort Key1:=ws.Range("A1"), Order1:=xlAscending, _
Key2:=ws.Range("H1"), Order2:=xlAscending, _
Header:=xlYes, OrderCustom:=1
End Select
Next
End Sub

If this post helps click Yes

 

[Mike H]

Hi,

Try this

Sub SortData()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
Select Case ws.Name
Case "Percentages", "MasterNonDMA%", "MasterNonDMA", "MasterDMA%",
"MasterDMA", "Reciept Saxo", "Model Account"
'Do Nothing
Case Else
ws.Select
Cells.Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("H2") _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=1,
MatchCase:=False _
, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2:= _
xlSortNormal
End Select
Next
End Sub

Mike

 

[Jacob Skaria]

You should remove Exit sub for the 1st Case statement...

Sub SortData()
Dim ws As Worksheet

For Each ws In Worksheets
Select Case ws.Name
Case "Percentages", "MasterNonDMA%", "MasterNonDMA", _
"MasterDMA%", "MasterDMA", "Reciept Saxo", "Model Account"
'Do nothing
Case Else
ws.Cells.Sort Key1:=ws.Range("A1"), Order1:=xlAscending, _
Key2:=ws.Range("H1"), Order2:=xlAscending, _
Header:=xlYes, OrderCustom:=1
End Select
Next
End Sub

If this post helps click Yes

 

[PVANS]

Hi Jacob and Mike

Thank you so much, it is now working just wonderfully.

Thank you, once again
Regards