Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro that will sort and create different worksheets | Excel Programming | |||
Help:macro to auto sort data to different worksheets... | Excel Discussion (Misc queries) | |||
Macro to sort worksheets alphabetically | Excel Programming | |||
Macro to sort Worksheets | Excel Programming | |||
Excel macro to loop through worksheets and graph data from each worksheet | Excel Programming |