| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tidying Recorded Macro Code
Hi There, Hopefully someone can help me, I have recorded the macro below
which works fine, however it's very large. Basically all I need is for the the following sheets. ("UK Purchasing Data"); ("EJ200 Data Sheet"); ("UK Purchasing Data 1203"); ("UK Purchasing Data 1103"); ("UK Purchasing Data 2103"); ("UK Purchasing Data 1303"); ("UK Purchasing Data Spares"). To be sorted ascending order on range L2 any help would be appreciated. Big H Sub Sort_Desending_Order() ' ' Sort_Desending_Order Macro ' Macro recorded 17/08/2007 by BigH ' ' Range("A19").Select Selection.Sort Key1:=Range("L20"), Order1:=xlDescending, Key2:=Range( _ "E20"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _ :=False, Orientation:=xlTopToBottom Sheets("EJ200 Data Sheet").Select Range("E1").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Sheets("UK Purchasing Data 1203").Select Range("G1").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Sheets("UK Purchasing Data 1103").Select Range("H1").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Sheets("UK Purchasing Data 2103").Select Range("L1").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Sheets("UK Purchasing Data 1303").Select Range("L2").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Sheets("UK Purchasing Data Spares").Select Range("L1").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Key2:=Range("E2") _ , Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom ActiveWindow.ScrollWorkbookTabs Position:=xlFirst Sheets("UK Purchasing Data").Select Range("A1").Select End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Tidying Recorded Macro Code
Here is one method putting sheet names in an array and using a for loop
Sub sort() sheetnames = Array("UK Purchasing Data", _ "EJ200 Data Sheet", "UK Purchasing Data 1203", _ "UK Purchasing Data 1103", "UK Purchasing Data 2103", _ "UK Purchasing", "UK Purchasing Data 1303", _ "UK Purchasing Data Spares") For shnames = 0 To (UBound(sheetnames) - 1) Sheets(sheetnames(shnames)).sort _ Key1:=Range("L2"), _ Order1:=xlDescending, _ Header:=xlGuess, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom Next shnames End Sub "Big H" wrote: Hi There, Hopefully someone can help me, I have recorded the macro below which works fine, however it's very large. Basically all I need is for the the following sheets. ("UK Purchasing Data"); ("EJ200 Data Sheet"); ("UK Purchasing Data 1203"); ("UK Purchasing Data 1103"); ("UK Purchasing Data 2103"); ("UK Purchasing Data 1303"); ("UK Purchasing Data Spares"). To be sorted ascending order on range L2 any help would be appreciated. Big H Sub Sort_Desending_Order() ' ' Sort_Desending_Order Macro ' Macro recorded 17/08/2007 by BigH ' ' Range("A19").Select Selection.Sort Key1:=Range("L20"), Order1:=xlDescending, Key2:=Range( _ "E20"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _ :=False, Orientation:=xlTopToBottom Sheets("EJ200 Data Sheet").Select Range("E1").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Sheets("UK Purchasing Data 1203").Select Range("G1").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Sheets("UK Purchasing Data 1103").Select Range("H1").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Sheets("UK Purchasing Data 2103").Select Range("L1").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Sheets("UK Purchasing Data 1303").Select Range("L2").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Sheets("UK Purchasing Data Spares").Select Range("L1").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Key2:=Range("E2") _ , Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom ActiveWindow.ScrollWorkbookTabs Position:=xlFirst Sheets("UK Purchasing Data").Select Range("A1").Select End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Tidying Recorded Macro Code
Big H
I don't understand what the code is doing. It selects one cell, then sorts the selection. Obviously you can't sort a range of one cell. I take it that you want to sort something in a number of sheets. Do you want to sort the same range in every sheet? What range? Do those ranges include headers? I would use a For loop through an array of all the sheets you want to sort, then sort each range. If all the ranges are the same then that's all you would need. If not, I would use a Select Case construct to define the range for each sheet. Post back if you need more. If you post back, include more detail about what the ranges are or, if they are variable, include an explanation of how the range-to-sort is determined for each sheet. HTH Otto "Big H" wrote in message ... Hi There, Hopefully someone can help me, I have recorded the macro below which works fine, however it's very large. Basically all I need is for the the following sheets. ("UK Purchasing Data"); ("EJ200 Data Sheet"); ("UK Purchasing Data 1203"); ("UK Purchasing Data 1103"); ("UK Purchasing Data 2103"); ("UK Purchasing Data 1303"); ("UK Purchasing Data Spares"). To be sorted ascending order on range L2 any help would be appreciated. Big H Sub Sort_Desending_Order() ' ' Sort_Desending_Order Macro ' Macro recorded 17/08/2007 by BigH ' ' Range("A19").Select Selection.Sort Key1:=Range("L20"), Order1:=xlDescending, Key2:=Range( _ "E20"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _ :=False, Orientation:=xlTopToBottom Sheets("EJ200 Data Sheet").Select Range("E1").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Sheets("UK Purchasing Data 1203").Select Range("G1").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Sheets("UK Purchasing Data 1103").Select Range("H1").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Sheets("UK Purchasing Data 2103").Select Range("L1").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Sheets("UK Purchasing Data 1303").Select Range("L2").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Sheets("UK Purchasing Data Spares").Select Range("L1").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Key2:=Range("E2") _ , Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom ActiveWindow.ScrollWorkbookTabs Position:=xlFirst Sheets("UK Purchasing Data").Select Range("A1").Select End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Tidying Recorded Macro Code
I didn't test my last posting I forgot the cells. Sort needs a range and a
sheet is not a range. Your need sheets("sheet1").cells which is a range. Don forgot the cells also so his code will also give an error. Otto: You need tried to record a sort macro. the original code posted does work. The record macro when you select a column only puts in L1 as the sort range, but really sorts the whole column. Sub sort() sheetnames = Array("UK Purchasing Data", _ "EJ200 Data Sheet", "UK Purchasing Data 1203", _ "UK Purchasing Data 1103", "UK Purchasing Data 2103", _ "UK Purchasing", "UK Purchasing Data 1303", _ "UK Purchasing Data Spares") For shnames = 0 To (UBound(sheetnames) - 1) Sheets(sheetnames(shnames)).cells.sort _ Key1:=Range("L2"), _ Order1:=xlDescending, _ Header:=xlGuess, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom Next shnames "Don Guillett" wrote: Without looking too closely or testing something like this Put the sheet names in an array myarray = Array("sheet6", "sheet13", "etc") For Each sh In myarray 'MsgBox sh sh.range("yourrange").Sort Key1:=sh.Range("L20"), Order1:=xlDescending, Key2:= _ sh.Range("E20"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Next -- Don Guillett Microsoft MVP Excel SalesAid Software "Big H" wrote in message ... Hi There, Hopefully someone can help me, I have recorded the macro below which works fine, however it's very large. Basically all I need is for the the following sheets. ("UK Purchasing Data"); ("EJ200 Data Sheet"); ("UK Purchasing Data 1203"); ("UK Purchasing Data 1103"); ("UK Purchasing Data 2103"); ("UK Purchasing Data 1303"); ("UK Purchasing Data Spares"). To be sorted ascending order on range L2 any help would be appreciated. Big H Sub Sort_Desending_Order() ' ' Sort_Desending_Order Macro ' Macro recorded 17/08/2007 by BigH ' ' Range("A19").Select Selection.Sort Key1:=Range("L20"), Order1:=xlDescending, Key2:=Range( _ "E20"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _ :=False, Orientation:=xlTopToBottom myarray = Array("sheet6", "sheet13") For Each sh In myarray MsgBox sh Next Sheets("UK Purchasing Data 1203").Select Range("G1").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Sheets("UK Purchasing Data 1103").Select Range("H1").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Sheets("UK Purchasing Data 2103").Select Range("L1").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Sheets("UK Purchasing Data 1303").Select Range("L2").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Sheets("UK Purchasing Data Spares").Select Range("L1").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Key2:=Range("E2") _ , Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom ActiveWindow.ScrollWorkbookTabs Position:=xlFirst Sheets("UK Purchasing Data").Select Range("A1").Select End Sub |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Tidying Recorded Macro Code
And the key1 has to be on the same sheet:
Sheets(sheetnames(shnames)).cells.sort _ Key1:=Sheets(sheetnames(shnames)).Range("L2"), _ .... Joel wrote: I didn't test my last posting I forgot the cells. Sort needs a range and a sheet is not a range. Your need sheets("sheet1").cells which is a range. Don forgot the cells also so his code will also give an error. Otto: You need tried to record a sort macro. the original code posted does work. The record macro when you select a column only puts in L1 as the sort range, but really sorts the whole column. Sub sort() sheetnames = Array("UK Purchasing Data", _ "EJ200 Data Sheet", "UK Purchasing Data 1203", _ "UK Purchasing Data 1103", "UK Purchasing Data 2103", _ "UK Purchasing", "UK Purchasing Data 1303", _ "UK Purchasing Data Spares") For shnames = 0 To (UBound(sheetnames) - 1) Sheets(sheetnames(shnames)).cells.sort _ Key1:=Range("L2"), _ Order1:=xlDescending, _ Header:=xlGuess, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom Next shnames "Don Guillett" wrote: Without looking too closely or testing something like this Put the sheet names in an array myarray = Array("sheet6", "sheet13", "etc") For Each sh In myarray 'MsgBox sh sh.range("yourrange").Sort Key1:=sh.Range("L20"), Order1:=xlDescending, Key2:= _ sh.Range("E20"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Next -- Don Guillett Microsoft MVP Excel SalesAid Software "Big H" wrote in message ... Hi There, Hopefully someone can help me, I have recorded the macro below which works fine, however it's very large. Basically all I need is for the the following sheets. ("UK Purchasing Data"); ("EJ200 Data Sheet"); ("UK Purchasing Data 1203"); ("UK Purchasing Data 1103"); ("UK Purchasing Data 2103"); ("UK Purchasing Data 1303"); ("UK Purchasing Data Spares"). To be sorted ascending order on range L2 any help would be appreciated. Big H Sub Sort_Desending_Order() ' ' Sort_Desending_Order Macro ' Macro recorded 17/08/2007 by BigH ' ' Range("A19").Select Selection.Sort Key1:=Range("L20"), Order1:=xlDescending, Key2:=Range( _ "E20"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _ :=False, Orientation:=xlTopToBottom myarray = Array("sheet6", "sheet13") For Each sh In myarray MsgBox sh Next Sheets("UK Purchasing Data 1203").Select Range("G1").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Sheets("UK Purchasing Data 1103").Select Range("H1").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Sheets("UK Purchasing Data 2103").Select Range("L1").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Sheets("UK Purchasing Data 1303").Select Range("L2").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Sheets("UK Purchasing Data Spares").Select Range("L1").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Key2:=Range("E2") _ , Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom ActiveWindow.ScrollWorkbookTabs Position:=xlFirst Sheets("UK Purchasing Data").Select Range("A1").Select End Sub -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Tidying Recorded Macro Code
Joel,
I have tried running your code, however it comes up with a runtime error 438 and highlights the following code yellow Sheets(sheetnames(shnames)).sort _ Key1:=Range("L2"), _ Order1:=xlDescending, _ Header:=xlGuess, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom Any suggestions Big H "Joel" wrote in message ... Here is one method putting sheet names in an array and using a for loop Sub sort() sheetnames = Array("UK Purchasing Data", _ "EJ200 Data Sheet", "UK Purchasing Data 1203", _ "UK Purchasing Data 1103", "UK Purchasing Data 2103", _ "UK Purchasing", "UK Purchasing Data 1303", _ "UK Purchasing Data Spares") For shnames = 0 To (UBound(sheetnames) - 1) Sheets(sheetnames(shnames)).sort _ Key1:=Range("L2"), _ Order1:=xlDescending, _ Header:=xlGuess, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom Next shnames End Sub "Big H" wrote: Hi There, Hopefully someone can help me, I have recorded the macro below which works fine, however it's very large. Basically all I need is for the the following sheets. ("UK Purchasing Data"); ("EJ200 Data Sheet"); ("UK Purchasing Data 1203"); ("UK Purchasing Data 1103"); ("UK Purchasing Data 2103"); ("UK Purchasing Data 1303"); ("UK Purchasing Data Spares"). To be sorted ascending order on range L2 any help would be appreciated. Big H Sub Sort_Desending_Order() ' ' Sort_Desending_Order Macro ' Macro recorded 17/08/2007 by BigH ' ' Range("A19").Select Selection.Sort Key1:=Range("L20"), Order1:=xlDescending, Key2:=Range( _ "E20"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _ :=False, Orientation:=xlTopToBottom Sheets("EJ200 Data Sheet").Select Range("E1").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Sheets("UK Purchasing Data 1203").Select Range("G1").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Sheets("UK Purchasing Data 1103").Select Range("H1").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Sheets("UK Purchasing Data 2103").Select Range("L1").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Sheets("UK Purchasing Data 1303").Select Range("L2").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Sheets("UK Purchasing Data Spares").Select Range("L1").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Key2:=Range("E2") _ , Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom ActiveWindow.ScrollWorkbookTabs Position:=xlFirst Sheets("UK Purchasing Data").Select Range("A1").Select End Sub |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Tidying Recorded Macro Code
sh.range("yourrange").Sort Key1:=sh.Range("L20"),
Don forgot the cells also so his code will also give an error. I did? -- Don Guillett Microsoft MVP Excel SalesAid Software "Joel" wrote in message ... I didn't test my last posting I forgot the cells. Sort needs a range and a sheet is not a range. Your need sheets("sheet1").cells which is a range. Don forgot the cells also so his code will also give an error. Otto: You need tried to record a sort macro. the original code posted does work. The record macro when you select a column only puts in L1 as the sort range, but really sorts the whole column. Sub sort() sheetnames = Array("UK Purchasing Data", _ "EJ200 Data Sheet", "UK Purchasing Data 1203", _ "UK Purchasing Data 1103", "UK Purchasing Data 2103", _ "UK Purchasing", "UK Purchasing Data 1303", _ "UK Purchasing Data Spares") For shnames = 0 To (UBound(sheetnames) - 1) Sheets(sheetnames(shnames)).cells.sort _ Key1:=Range("L2"), _ Order1:=xlDescending, _ Header:=xlGuess, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom Next shnames "Don Guillett" wrote: Without looking too closely or testing something like this Put the sheet names in an array myarray = Array("sheet6", "sheet13", "etc") For Each sh In myarray 'MsgBox sh sh.range("yourrange").Sort Key1:=sh.Range("L20"), Order1:=xlDescending, Key2:= _ sh.Range("E20"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Next -- Don Guillett Microsoft MVP Excel SalesAid Software "Big H" wrote in message ... Hi There, Hopefully someone can help me, I have recorded the macro below which works fine, however it's very large. Basically all I need is for the the following sheets. ("UK Purchasing Data"); ("EJ200 Data Sheet"); ("UK Purchasing Data 1203"); ("UK Purchasing Data 1103"); ("UK Purchasing Data 2103"); ("UK Purchasing Data 1303"); ("UK Purchasing Data Spares"). To be sorted ascending order on range L2 any help would be appreciated. Big H Sub Sort_Desending_Order() ' ' Sort_Desending_Order Macro ' Macro recorded 17/08/2007 by BigH ' ' Range("A19").Select Selection.Sort Key1:=Range("L20"), Order1:=xlDescending, Key2:=Range( _ "E20"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _ :=False, Orientation:=xlTopToBottom myarray = Array("sheet6", "sheet13") For Each sh In myarray MsgBox sh Next Sheets("UK Purchasing Data 1203").Select Range("G1").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Sheets("UK Purchasing Data 1103").Select Range("H1").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Sheets("UK Purchasing Data 2103").Select Range("L1").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Sheets("UK Purchasing Data 1303").Select Range("L2").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Sheets("UK Purchasing Data Spares").Select Range("L1").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Key2:=Range("E2") _ , Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom ActiveWindow.ScrollWorkbookTabs Position:=xlFirst Sheets("UK Purchasing Data").Select Range("A1").Select End Sub |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Tidying Recorded Macro Code
Joel, sorry for being a nuisance, the code is 99.5% working, the only
problem I have is that the last sheet doesnt get sorted ("UK Purchasing Data Spares"). I know it's because of @For shnames = 0 To (UBound(sheetnames) - 1)" as when I change - 1 to - 2, the sort misses the last 2 sheets in the array. Do you have any suggestions on how to fix this. regards Big H Sub sort() sheetnames = Array("UK Purchasing Data", _ "EJ200 Data Sheet", "UK Purchasing Data 1203", _ "UK Purchasing Data 1103", "UK Purchasing Data 2103", _ "UK Purchasing Data 1303", "UK Purchasing Data Spares") For shnames = 0 To (UBound(sheetnames) - 1) Sheets(sheetnames(shnames)).Cells.sort _ Key1:=Sheets(sheetnames(shnames)).Range("L2"), _ Order1:=xlDescending, _ Header:=xlGuess, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom Next shnames End Sub "Joel" wrote in message ... I didn't test my last posting I forgot the cells. Sort needs a range and a sheet is not a range. Your need sheets("sheet1").cells which is a range. Don forgot the cells also so his code will also give an error. Otto: You need tried to record a sort macro. the original code posted does work. The record macro when you select a column only puts in L1 as the sort range, but really sorts the whole column. Sub sort() sheetnames = Array("UK Purchasing Data", _ "EJ200 Data Sheet", "UK Purchasing Data 1203", _ "UK Purchasing Data 1103", "UK Purchasing Data 2103", _ "UK Purchasing", "UK Purchasing Data 1303", _ "UK Purchasing Data Spares") For shnames = 0 To (UBound(sheetnames) - 1) Sheets(sheetnames(shnames)).cells.sort _ Key1:=Range("L2"), _ Order1:=xlDescending, _ Header:=xlGuess, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom Next shnames "Don Guillett" wrote: Without looking too closely or testing something like this Put the sheet names in an array myarray = Array("sheet6", "sheet13", "etc") For Each sh In myarray 'MsgBox sh sh.range("yourrange").Sort Key1:=sh.Range("L20"), Order1:=xlDescending, Key2:= _ sh.Range("E20"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Next -- Don Guillett Microsoft MVP Excel SalesAid Software "Big H" wrote in message ... Hi There, Hopefully someone can help me, I have recorded the macro below which works fine, however it's very large. Basically all I need is for the the following sheets. ("UK Purchasing Data"); ("EJ200 Data Sheet"); ("UK Purchasing Data 1203"); ("UK Purchasing Data 1103"); ("UK Purchasing Data 2103"); ("UK Purchasing Data 1303"); ("UK Purchasing Data Spares"). To be sorted ascending order on range L2 any help would be appreciated. Big H Sub Sort_Desending_Order() ' ' Sort_Desending_Order Macro ' Macro recorded 17/08/2007 by BigH ' ' Range("A19").Select Selection.Sort Key1:=Range("L20"), Order1:=xlDescending, Key2:=Range( _ "E20"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _ :=False, Orientation:=xlTopToBottom myarray = Array("sheet6", "sheet13") For Each sh In myarray MsgBox sh Next Sheets("UK Purchasing Data 1203").Select Range("G1").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Sheets("UK Purchasing Data 1103").Select Range("H1").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Sheets("UK Purchasing Data 2103").Select Range("L1").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Sheets("UK Purchasing Data 1303").Select Range("L2").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Sheets("UK Purchasing Data Spares").Select Range("L1").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Key2:=Range("E2") _ , Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom ActiveWindow.ScrollWorkbookTabs Position:=xlFirst Sheets("UK Purchasing Data").Select Range("A1").Select End Sub |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Tidying Recorded Macro Code
You could use:
For shnames = lbound(sheetnames) To (UBound(sheetnames) Big H wrote: Joel, sorry for being a nuisance, the code is 99.5% working, the only problem I have is that the last sheet doesnt get sorted ("UK Purchasing Data Spares"). I know it's because of @For shnames = 0 To (UBound(sheetnames) - 1)" as when I change - 1 to - 2, the sort misses the last 2 sheets in the array. Do you have any suggestions on how to fix this. regards Big H Sub sort() sheetnames = Array("UK Purchasing Data", _ "EJ200 Data Sheet", "UK Purchasing Data 1203", _ "UK Purchasing Data 1103", "UK Purchasing Data 2103", _ "UK Purchasing Data 1303", "UK Purchasing Data Spares") For shnames = 0 To (UBound(sheetnames) - 1) Sheets(sheetnames(shnames)).Cells.sort _ Key1:=Sheets(sheetnames(shnames)).Range("L2"), _ Order1:=xlDescending, _ Header:=xlGuess, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom Next shnames End Sub "Joel" wrote in message ... I didn't test my last posting I forgot the cells. Sort needs a range and a sheet is not a range. Your need sheets("sheet1").cells which is a range. Don forgot the cells also so his code will also give an error. Otto: You need tried to record a sort macro. the original code posted does work. The record macro when you select a column only puts in L1 as the sort range, but really sorts the whole column. Sub sort() sheetnames = Array("UK Purchasing Data", _ "EJ200 Data Sheet", "UK Purchasing Data 1203", _ "UK Purchasing Data 1103", "UK Purchasing Data 2103", _ "UK Purchasing", "UK Purchasing Data 1303", _ "UK Purchasing Data Spares") For shnames = 0 To (UBound(sheetnames) - 1) Sheets(sheetnames(shnames)).cells.sort _ Key1:=Range("L2"), _ Order1:=xlDescending, _ Header:=xlGuess, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom Next shnames "Don Guillett" wrote: Without looking too closely or testing something like this Put the sheet names in an array myarray = Array("sheet6", "sheet13", "etc") For Each sh In myarray 'MsgBox sh sh.range("yourrange").Sort Key1:=sh.Range("L20"), Order1:=xlDescending, Key2:= _ sh.Range("E20"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Next -- Don Guillett Microsoft MVP Excel SalesAid Software "Big H" wrote in message ... Hi There, Hopefully someone can help me, I have recorded the macro below which works fine, however it's very large. Basically all I need is for the the following sheets. ("UK Purchasing Data"); ("EJ200 Data Sheet"); ("UK Purchasing Data 1203"); ("UK Purchasing Data 1103"); ("UK Purchasing Data 2103"); ("UK Purchasing Data 1303"); ("UK Purchasing Data Spares"). To be sorted ascending order on range L2 any help would be appreciated. Big H Sub Sort_Desending_Order() ' ' Sort_Desending_Order Macro ' Macro recorded 17/08/2007 by BigH ' ' Range("A19").Select Selection.Sort Key1:=Range("L20"), Order1:=xlDescending, Key2:=Range( _ "E20"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _ :=False, Orientation:=xlTopToBottom myarray = Array("sheet6", "sheet13") For Each sh In myarray MsgBox sh Next Sheets("UK Purchasing Data 1203").Select Range("G1").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Sheets("UK Purchasing Data 1103").Select Range("H1").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Sheets("UK Purchasing Data 2103").Select Range("L1").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Sheets("UK Purchasing Data 1303").Select Range("L2").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Sheets("UK Purchasing Data Spares").Select Range("L1").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Key2:=Range("E2") _ , Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom ActiveWindow.ScrollWorkbookTabs Position:=xlFirst Sheets("UK Purchasing Data").Select Range("A1").Select End Sub -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Tidying Recorded Macro Code
Thanks Dave, It now works perfectly, though I have another problem with this
code. What I would like to to with this same array is delete rows 1:19 and then sort the data (as per the perfectly working text. I tried the following code Sheets(sheetnames(shnames)).Range("1:19").Select Selection.Delete Shift:=xlUp after the code you wrote below, however it comes up with a "Select method of range error" do you have any ideas? regards Big H "Dave Peterson" wrote in message ... You could use: For shnames = lbound(sheetnames) To (UBound(sheetnames) Big H wrote: Joel, sorry for being a nuisance, the code is 99.5% working, the only problem I have is that the last sheet doesnt get sorted ("UK Purchasing Data Spares"). I know it's because of @For shnames = 0 To (UBound(sheetnames) - 1)" as when I change - 1 to - 2, the sort misses the last 2 sheets in the array. Do you have any suggestions on how to fix this. regards Big H Sub sort() sheetnames = Array("UK Purchasing Data", _ "EJ200 Data Sheet", "UK Purchasing Data 1203", _ "UK Purchasing Data 1103", "UK Purchasing Data 2103", _ "UK Purchasing Data 1303", "UK Purchasing Data Spares") For shnames = 0 To (UBound(sheetnames) - 1) Sheets(sheetnames(shnames)).Cells.sort _ Key1:=Sheets(sheetnames(shnames)).Range("L2"), _ Order1:=xlDescending, _ Header:=xlGuess, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom Next shnames End Sub "Joel" wrote in message ... I didn't test my last posting I forgot the cells. Sort needs a range and a sheet is not a range. Your need sheets("sheet1").cells which is a range. Don forgot the cells also so his code will also give an error. Otto: You need tried to record a sort macro. the original code posted does work. The record macro when you select a column only puts in L1 as the sort range, but really sorts the whole column. Sub sort() sheetnames = Array("UK Purchasing Data", _ "EJ200 Data Sheet", "UK Purchasing Data 1203", _ "UK Purchasing Data 1103", "UK Purchasing Data 2103", _ "UK Purchasing", "UK Purchasing Data 1303", _ "UK Purchasing Data Spares") For shnames = 0 To (UBound(sheetnames) - 1) Sheets(sheetnames(shnames)).cells.sort _ Key1:=Range("L2"), _ Order1:=xlDescending, _ Header:=xlGuess, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom Next shnames "Don Guillett" wrote: Without looking too closely or testing something like this Put the sheet names in an array myarray = Array("sheet6", "sheet13", "etc") For Each sh In myarray 'MsgBox sh sh.range("yourrange").Sort Key1:=sh.Range("L20"), Order1:=xlDescending, Key2:= _ sh.Range("E20"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Next -- Don Guillett Microsoft MVP Excel SalesAid Software "Big H" wrote in message ... Hi There, Hopefully someone can help me, I have recorded the macro below which works fine, however it's very large. Basically all I need is for the the following sheets. ("UK Purchasing Data"); ("EJ200 Data Sheet"); ("UK Purchasing Data 1203"); ("UK Purchasing Data 1103"); ("UK Purchasing Data 2103"); ("UK Purchasing Data 1303"); ("UK Purchasing Data Spares"). To be sorted ascending order on range L2 any help would be appreciated. Big H Sub Sort_Desending_Order() ' ' Sort_Desending_Order Macro ' Macro recorded 17/08/2007 by BigH ' ' Range("A19").Select Selection.Sort Key1:=Range("L20"), Order1:=xlDescending, Key2:=Range( _ "E20"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _ :=False, Orientation:=xlTopToBottom myarray = Array("sheet6", "sheet13") For Each sh In myarray MsgBox sh Next Sheets("UK Purchasing Data 1203").Select Range("G1").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Sheets("UK Purchasing Data 1103").Select Range("H1").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Sheets("UK Purchasing Data 2103").Select Range("L1").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Sheets("UK Purchasing Data 1303").Select Range("L2").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Sheets("UK Purchasing Data Spares").Select Range("L1").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Key2:=Range("E2") _ , Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom ActiveWindow.ScrollWorkbookTabs Position:=xlFirst Sheets("UK Purchasing Data").Select Range("A1").Select End Sub -- Dave Peterson |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Tidying Recorded Macro Code
Don't select it. Just delete it.
Sheets(sheetnames(shnames)).Range("1:19").Delete Shift:=xlUp Big H wrote: Thanks Dave, It now works perfectly, though I have another problem with this code. What I would like to to with this same array is delete rows 1:19 and then sort the data (as per the perfectly working text. I tried the following code Sheets(sheetnames(shnames)).Range("1:19").Select Selection.Delete Shift:=xlUp after the code you wrote below, however it comes up with a "Select method of range error" do you have any ideas? regards Big H "Dave Peterson" wrote in message ... You could use: For shnames = lbound(sheetnames) To (UBound(sheetnames) Big H wrote: Joel, sorry for being a nuisance, the code is 99.5% working, the only problem I have is that the last sheet doesnt get sorted ("UK Purchasing Data Spares"). I know it's because of @For shnames = 0 To (UBound(sheetnames) - 1)" as when I change - 1 to - 2, the sort misses the last 2 sheets in the array. Do you have any suggestions on how to fix this. regards Big H Sub sort() sheetnames = Array("UK Purchasing Data", _ "EJ200 Data Sheet", "UK Purchasing Data 1203", _ "UK Purchasing Data 1103", "UK Purchasing Data 2103", _ "UK Purchasing Data 1303", "UK Purchasing Data Spares") For shnames = 0 To (UBound(sheetnames) - 1) Sheets(sheetnames(shnames)).Cells.sort _ Key1:=Sheets(sheetnames(shnames)).Range("L2"), _ Order1:=xlDescending, _ Header:=xlGuess, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom Next shnames End Sub "Joel" wrote in message ... I didn't test my last posting I forgot the cells. Sort needs a range and a sheet is not a range. Your need sheets("sheet1").cells which is a range. Don forgot the cells also so his code will also give an error. Otto: You need tried to record a sort macro. the original code posted does work. The record macro when you select a column only puts in L1 as the sort range, but really sorts the whole column. Sub sort() sheetnames = Array("UK Purchasing Data", _ "EJ200 Data Sheet", "UK Purchasing Data 1203", _ "UK Purchasing Data 1103", "UK Purchasing Data 2103", _ "UK Purchasing", "UK Purchasing Data 1303", _ "UK Purchasing Data Spares") For shnames = 0 To (UBound(sheetnames) - 1) Sheets(sheetnames(shnames)).cells.sort _ Key1:=Range("L2"), _ Order1:=xlDescending, _ Header:=xlGuess, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom Next shnames "Don Guillett" wrote: Without looking too closely or testing something like this Put the sheet names in an array myarray = Array("sheet6", "sheet13", "etc") For Each sh In myarray 'MsgBox sh sh.range("yourrange").Sort Key1:=sh.Range("L20"), Order1:=xlDescending, Key2:= _ sh.Range("E20"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Next -- Don Guillett Microsoft MVP Excel SalesAid Software "Big H" wrote in message ... Hi There, Hopefully someone can help me, I have recorded the macro below which works fine, however it's very large. Basically all I need is for the the following sheets. ("UK Purchasing Data"); ("EJ200 Data Sheet"); ("UK Purchasing Data 1203"); ("UK Purchasing Data 1103"); ("UK Purchasing Data 2103"); ("UK Purchasing Data 1303"); ("UK Purchasing Data Spares"). To be sorted ascending order on range L2 any help would be appreciated. Big H Sub Sort_Desending_Order() ' ' Sort_Desending_Order Macro ' Macro recorded 17/08/2007 by BigH ' ' Range("A19").Select Selection.Sort Key1:=Range("L20"), Order1:=xlDescending, Key2:=Range( _ "E20"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _ :=False, Orientation:=xlTopToBottom myarray = Array("sheet6", "sheet13") For Each sh In myarray MsgBox sh Next Sheets("UK Purchasing Data 1203").Select Range("G1").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Sheets("UK Purchasing Data 1103").Select Range("H1").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Sheets("UK Purchasing Data 2103").Select Range("L1").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Sheets("UK Purchasing Data 1303").Select Range("L2").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Sheets("UK Purchasing Data Spares").Select Range("L1").Select Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Key2:=Range("E2") _ , Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom ActiveWindow.ScrollWorkbookTabs Position:=xlFirst Sheets("UK Purchasing Data").Select Range("A1").Select End Sub -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Simplifying Excel Recorded Macro Code | Excel Programming | |||
| Help with tidying up code please | Excel Programming | |||
| Call recorded macro from VBA code | Excel Programming | |||
| Editing recorded macro code. Referencing workbook name in functio | Excel Programming | |||
| looking to simplify a recorded macro with code | Excel Programming | |||
Linear Mode
