ExcelBanter - Macro to Loop through and sort worksheets

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Macro to Loop through and sort worksheets (https://www.excelbanter.com/excel-programming/434314-macro-loop-through-sort-worksheets.html)

Macro to Loop through and sort worksheets

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

Macro to Loop through and sort worksheets

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
---------------
Jacob Skaria

"PVANS" wrote:

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

Macro to Loop through and sort worksheets

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

"PVANS" wrote:

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

Macro to Loop through and sort worksheets

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
---------------
Jacob Skaria

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria

"PVANS" wrote:

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

Macro to Loop through and sort worksheets

Hi Jacob and Mike

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

Thank you, once again
Regards

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria

"PVANS" wrote:

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