Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro code to sort a range
Hello All,
I tried searching in the group but found nothing of what I was looking for. I have a .xls file with 10 worksheets in it. assume that sheets are named as a,b,c,d to j. I am trying to make a macro which will sort a range c2:h30 on sheet "a" then range c2:h15 on sheet "b" then range c2:h45 on sheet "c" and so on.[ I know exact range for each sheet so nothing is required to be calculated dynamically and also the sorting will be based upon the cell h2]. I dont want to use loop because I would like to skip some sheets some times. If any one can tell me how to write this macro then I will be highly grateful to him. Teach me for one sheet and I will apply best of my mind to make it work for others too. Thanks for listening (or reading so long). |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro code to sort a range
Sub gsnu()
Sheets("a").Select Range("C2:H30").Select Application.CutCopyMode = False Selection.Sort Key1:=Range("H2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub -- Gary''s Student "ashish128" wrote: Hello All, I tried searching in the group but found nothing of what I was looking for. I have a .xls file with 10 worksheets in it. assume that sheets are named as a,b,c,d to j. I am trying to make a macro which will sort a range c2:h30 on sheet "a" then range c2:h15 on sheet "b" then range c2:h45 on sheet "c" and so on.[ I know exact range for each sheet so nothing is required to be calculated dynamically and also the sorting will be based upon the cell h2]. I dont want to use loop because I would like to skip some sheets some times. If any one can tell me how to write this macro then I will be highly grateful to him. Teach me for one sheet and I will apply best of my mind to make it work for others too. Thanks for listening (or reading so long). |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro code to sort a range
Hi,
I modified your code for actual values but on running it gives an error, "Run Time Error 1004" Application defined or object defined error Following is modified code Sub gsnu() Sheets("wax").Select Range("C2:AN11").Select Application.CutCopyMode = False Selection.Sort Key1:=Range("AK2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub The Range is found selected when i stop the debugger. Also can u tell me what is "xlSortNormal" and "matchcase" for and difference between Order1 and orientation Gary''s Student wrote: Sub gsnu() Sheets("a").Select Range("C2:H30").Select Application.CutCopyMode = False Selection.Sort Key1:=Range("H2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub -- Gary''s Student "ashish128" wrote: Hello All, I tried searching in the group but found nothing of what I was looking for. I have a .xls file with 10 worksheets in it. assume that sheets are named as a,b,c,d to j. I am trying to make a macro which will sort a range c2:h30 on sheet "a" then range c2:h15 on sheet "b" then range c2:h45 on sheet "c" and so on.[ I know exact range for each sheet so nothing is required to be calculated dynamically and also the sorting will be based upon the cell h2]. I dont want to use loop because I would like to skip some sheets some times. If any one can tell me how to write this macro then I will be highly grateful to him. Teach me for one sheet and I will apply best of my mind to make it work for others too. Thanks for listening (or reading so long). |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro code to sort a range
I can't get your error message to appear. The code I posted is a direct copy
of the code generated by the Macro Recorder. -- Gary's Student "ashish128" wrote: Hi, I modified your code for actual values but on running it gives an error, "Run Time Error 1004" Application defined or object defined error Following is modified code Sub gsnu() Sheets("wax").Select Range("C2:AN11").Select Application.CutCopyMode = False Selection.Sort Key1:=Range("AK2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub The Range is found selected when i stop the debugger. Also can u tell me what is "xlSortNormal" and "matchcase" for and difference between Order1 and orientation Gary''s Student wrote: Sub gsnu() Sheets("a").Select Range("C2:H30").Select Application.CutCopyMode = False Selection.Sort Key1:=Range("H2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub -- Gary''s Student "ashish128" wrote: Hello All, I tried searching in the group but found nothing of what I was looking for. I have a .xls file with 10 worksheets in it. assume that sheets are named as a,b,c,d to j. I am trying to make a macro which will sort a range c2:h30 on sheet "a" then range c2:h15 on sheet "b" then range c2:h45 on sheet "c" and so on.[ I know exact range for each sheet so nothing is required to be calculated dynamically and also the sorting will be based upon the cell h2]. I dont want to use loop because I would like to skip some sheets some times. If any one can tell me how to write this macro then I will be highly grateful to him. Teach me for one sheet and I will apply best of my mind to make it work for others too. Thanks for listening (or reading so long). |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro code to sort a range
Hi, Thanks
You gave me the Idea. I self imitated the macro recording and found that instead of "Range("AK2")" I must write "Range("AK3")" to make it work. I dont know why i had to choose a value cell instead of a header cell to make it work but as for now it works and all the thanks go to you. Thanks again Gary''s Student wrote: I can't get your error message to appear. The code I posted is a direct copy of the code generated by the Macro Recorder. -- Gary's Student "ashish128" wrote: Hi, I modified your code for actual values but on running it gives an error, "Run Time Error 1004" Application defined or object defined error Following is modified code Sub gsnu() Sheets("wax").Select Range("C2:AN11").Select Application.CutCopyMode = False Selection.Sort Key1:=Range("AK2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub The Range is found selected when i stop the debugger. Also can u tell me what is "xlSortNormal" and "matchcase" for and difference between Order1 and orientation Gary''s Student wrote: Sub gsnu() Sheets("a").Select Range("C2:H30").Select Application.CutCopyMode = False Selection.Sort Key1:=Range("H2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub -- Gary''s Student "ashish128" wrote: Hello All, I tried searching in the group but found nothing of what I was looking for. I have a .xls file with 10 worksheets in it. assume that sheets are named as a,b,c,d to j. I am trying to make a macro which will sort a range c2:h30 on sheet "a" then range c2:h15 on sheet "b" then range c2:h45 on sheet "c" and so on.[ I know exact range for each sheet so nothing is required to be calculated dynamically and also the sorting will be based upon the cell h2]. I dont want to use loop because I would like to skip some sheets some times. If any one can tell me how to write this macro then I will be highly grateful to him. Teach me for one sheet and I will apply best of my mind to make it work for others too. Thanks for listening (or reading so long). |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro code to sort a range
You are very welcome
-- Gary''s Student "ashish128" wrote: Hi, Thanks You gave me the Idea. I self imitated the macro recording and found that instead of "Range("AK2")" I must write "Range("AK3")" to make it work. I dont know why i had to choose a value cell instead of a header cell to make it work but as for now it works and all the thanks go to you. Thanks again Gary''s Student wrote: I can't get your error message to appear. The code I posted is a direct copy of the code generated by the Macro Recorder. -- Gary's Student "ashish128" wrote: Hi, I modified your code for actual values but on running it gives an error, "Run Time Error 1004" Application defined or object defined error Following is modified code Sub gsnu() Sheets("wax").Select Range("C2:AN11").Select Application.CutCopyMode = False Selection.Sort Key1:=Range("AK2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub The Range is found selected when i stop the debugger. Also can u tell me what is "xlSortNormal" and "matchcase" for and difference between Order1 and orientation Gary''s Student wrote: Sub gsnu() Sheets("a").Select Range("C2:H30").Select Application.CutCopyMode = False Selection.Sort Key1:=Range("H2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub -- Gary''s Student "ashish128" wrote: Hello All, I tried searching in the group but found nothing of what I was looking for. I have a .xls file with 10 worksheets in it. assume that sheets are named as a,b,c,d to j. I am trying to make a macro which will sort a range c2:h30 on sheet "a" then range c2:h15 on sheet "b" then range c2:h45 on sheet "c" and so on.[ I know exact range for each sheet so nothing is required to be calculated dynamically and also the sorting will be based upon the cell h2]. I dont want to use loop because I would like to skip some sheets some times. If any one can tell me how to write this macro then I will be highly grateful to him. Teach me for one sheet and I will apply best of my mind to make it work for others too. Thanks for listening (or reading so long). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VB code to copy sheet format to another sheet | Excel Discussion (Misc queries) | |||
Can someone help me put psuedo code into actual excel macro?? | Excel Discussion (Misc queries) | |||
Closing File Error | Excel Discussion (Misc queries) | |||
Highlight Range - wrong macro, please edit. | Excel Worksheet Functions | |||
Make Change Case in Excel a format rather than formula | Excel Worksheet Functions |