| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort WorkSheetName unknown
How can I make the following work on any worksheet and on whatever the new
range might be "A11:Axxx" - "A11:Jxxx" Range("A11").Select ActiveWorkbook.Worksheets("JAN").Sort.SortFields.C lear ActiveWorkbook.Worksheets("JAN").Sort.SortFields.A dd Key:=Range("A11:A68") _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("JAN").Sort .SetRange Range("A10:J68") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sort WorkSheetName unknown
instead of activeworkbook.worksheets(bla) you could use activesheet
which would take the sheet that has been activated the last as reference. and if you want to sort the whole used area you could use usedrange as range. If that doesn't help, try to give some more information. Carlo On Jan 9, 4:41*pm, Helmut wrote: How can I make the following work on any worksheet and on whatever the new range might be "A11:Axxx" - "A11:Jxxx" Range("A11").Select * * ActiveWorkbook.Worksheets("JAN").Sort.SortFields.C lear * * ActiveWorkbook.Worksheets("JAN").Sort.SortFields.A dd Key:=Range("A11:A68") _ * * * * , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal * *With ActiveWorkbook.Worksheets("JAN").Sort * * * * .SetRange Range("A10:J68") * * * * .Header = xlYes * * * * .MatchCase = False * * * * .Orientation = xlTopToBottom * * * * .SortMethod = xlPinYin * * * * .Apply * * End With |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sort WorkSheetName unknown
I tried but the "usedrange" doesn't work...I changed the lines:
Range("A11").Select Activesheet.Sort.SortFields.Clear Activesheet.Sort.SortFields.Add Key:=usedrange _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With Activesheet.Sort .SetRange usedrange .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With "carlo" wrote: instead of activeworkbook.worksheets(bla) you could use activesheet which would take the sheet that has been activated the last as reference. and if you want to sort the whole used area you could use usedrange as range. If that doesn't help, try to give some more information. Carlo On Jan 9, 4:41 pm, Helmut wrote: How can I make the following work on any worksheet and on whatever the new range might be "A11:Axxx" - "A11:Jxxx" Range("A11").Select ActiveWorkbook.Worksheets("JAN").Sort.SortFields.C lear ActiveWorkbook.Worksheets("JAN").Sort.SortFields.A dd Key:=Range("A11:A68") _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("JAN").Sort .SetRange Range("A10:J68") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sort WorkSheetName unknown
Hi Helmut
Try Sub test() Dim lr As Long, lc As Long lc = ActiveSheet.Cells.Find("*", [A11], , , xlByColumns, xlPrevious).Column lr = ActiveSheet.Cells.Find("*", [A11], , , xlByRows, xlPrevious).Row Range("A11").Select ActiveSheet.Sort.SortFields.Clear ActiveSheet.Sort.SortFields.Add _ Key:=Range(Cells(11, 1), Cells(lr, 1)) _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveSheet.Sort .SetRange Range(Cells(11, 1), Cells(lr, lc)) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub -- Regards Roger Govier "Helmut" wrote in message ... I tried but the "usedrange" doesn't work...I changed the lines: Range("A11").Select Activesheet.Sort.SortFields.Clear Activesheet.Sort.SortFields.Add Key:=usedrange _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With Activesheet.Sort .SetRange usedrange .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With "carlo" wrote: instead of activeworkbook.worksheets(bla) you could use activesheet which would take the sheet that has been activated the last as reference. and if you want to sort the whole used area you could use usedrange as range. If that doesn't help, try to give some more information. Carlo On Jan 9, 4:41 pm, Helmut wrote: How can I make the following work on any worksheet and on whatever the new range might be "A11:Axxx" - "A11:Jxxx" Range("A11").Select ActiveWorkbook.Worksheets("JAN").Sort.SortFields.C lear ActiveWorkbook.Worksheets("JAN").Sort.SortFields.A dd Key:=Range("A11:A68") _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("JAN").Sort .SetRange Range("A10:J68") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sort WorkSheetName unknown
Hi roger,
your suggestion runs without error but does NOTactually sort. Any further suggestions? "Roger Govier" wrote: Hi Helmut Try Sub test() Dim lr As Long, lc As Long lc = ActiveSheet.Cells.Find("*", [A11], , , xlByColumns, xlPrevious).Column lr = ActiveSheet.Cells.Find("*", [A11], , , xlByRows, xlPrevious).Row Range("A11").Select ActiveSheet.Sort.SortFields.Clear ActiveSheet.Sort.SortFields.Add _ Key:=Range(Cells(11, 1), Cells(lr, 1)) _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveSheet.Sort .SetRange Range(Cells(11, 1), Cells(lr, lc)) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub -- Regards Roger Govier "Helmut" wrote in message ... I tried but the "usedrange" doesn't work...I changed the lines: Range("A11").Select Activesheet.Sort.SortFields.Clear Activesheet.Sort.SortFields.Add Key:=usedrange _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With Activesheet.Sort .SetRange usedrange .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With "carlo" wrote: instead of activeworkbook.worksheets(bla) you could use activesheet which would take the sheet that has been activated the last as reference. and if you want to sort the whole used area you could use usedrange as range. If that doesn't help, try to give some more information. Carlo On Jan 9, 4:41 pm, Helmut wrote: How can I make the following work on any worksheet and on whatever the new range might be "A11:Axxx" - "A11:Jxxx" Range("A11").Select ActiveWorkbook.Worksheets("JAN").Sort.SortFields.C lear ActiveWorkbook.Worksheets("JAN").Sort.SortFields.A dd Key:=Range("A11:A68") _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("JAN").Sort .SetRange Range("A10:J68") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sort WorkSheetName unknown
Hi Helmut
It sorted fine on the sample of data I used to test it. Were you on the correct sheet when you ran the macro? What happens if you step through the macro with F8? What do you see in the immediate window for lr and lc? -- Regards Roger Govier "Helmut" wrote in message ... Hi roger, your suggestion runs without error but does NOTactually sort. Any further suggestions? "Roger Govier" wrote: Hi Helmut Try Sub test() Dim lr As Long, lc As Long lc = ActiveSheet.Cells.Find("*", [A11], , , xlByColumns, xlPrevious).Column lr = ActiveSheet.Cells.Find("*", [A11], , , xlByRows, xlPrevious).Row Range("A11").Select ActiveSheet.Sort.SortFields.Clear ActiveSheet.Sort.SortFields.Add _ Key:=Range(Cells(11, 1), Cells(lr, 1)) _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveSheet.Sort .SetRange Range(Cells(11, 1), Cells(lr, lc)) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub -- Regards Roger Govier "Helmut" wrote in message ... I tried but the "usedrange" doesn't work...I changed the lines: Range("A11").Select Activesheet.Sort.SortFields.Clear Activesheet.Sort.SortFields.Add Key:=usedrange _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With Activesheet.Sort .SetRange usedrange .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With "carlo" wrote: instead of activeworkbook.worksheets(bla) you could use activesheet which would take the sheet that has been activated the last as reference. and if you want to sort the whole used area you could use usedrange as range. If that doesn't help, try to give some more information. Carlo On Jan 9, 4:41 pm, Helmut wrote: How can I make the following work on any worksheet and on whatever the new range might be "A11:Axxx" - "A11:Jxxx" Range("A11").Select ActiveWorkbook.Worksheets("JAN").Sort.SortFields.C lear ActiveWorkbook.Worksheets("JAN").Sort.SortFields.A dd Key:=Range("A11:A68") _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("JAN").Sort .SetRange Range("A10:J68") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sort WorkSheetName unknown
Using F8 I see nothing happening on the window at all for lc nor for lr
"Roger Govier" wrote: Hi Helmut It sorted fine on the sample of data I used to test it. Were you on the correct sheet when you ran the macro? What happens if you step through the macro with F8? What do you see in the immediate window for lr and lc? -- Regards Roger Govier "Helmut" wrote in message ... Hi roger, your suggestion runs without error but does NOTactually sort. Any further suggestions? "Roger Govier" wrote: Hi Helmut Try Sub test() Dim lr As Long, lc As Long lc = ActiveSheet.Cells.Find("*", [A11], , , xlByColumns, xlPrevious).Column lr = ActiveSheet.Cells.Find("*", [A11], , , xlByRows, xlPrevious).Row Range("A11").Select ActiveSheet.Sort.SortFields.Clear ActiveSheet.Sort.SortFields.Add _ Key:=Range(Cells(11, 1), Cells(lr, 1)) _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveSheet.Sort .SetRange Range(Cells(11, 1), Cells(lr, lc)) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub -- Regards Roger Govier "Helmut" wrote in message ... I tried but the "usedrange" doesn't work...I changed the lines: Range("A11").Select Activesheet.Sort.SortFields.Clear Activesheet.Sort.SortFields.Add Key:=usedrange _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With Activesheet.Sort .SetRange usedrange .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With "carlo" wrote: instead of activeworkbook.worksheets(bla) you could use activesheet which would take the sheet that has been activated the last as reference. and if you want to sort the whole used area you could use usedrange as range. If that doesn't help, try to give some more information. Carlo On Jan 9, 4:41 pm, Helmut wrote: How can I make the following work on any worksheet and on whatever the new range might be "A11:Axxx" - "A11:Jxxx" Range("A11").Select ActiveWorkbook.Worksheets("JAN").Sort.SortFields.C lear ActiveWorkbook.Worksheets("JAN").Sort.SortFields.A dd Key:=Range("A11:A68") _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("JAN").Sort .SetRange Range("A10:J68") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sort WorkSheetName unknown
Hi Helmut
if you want, you can email the file to me and I will take a look. to send direct mail to roger at technology4u dot co dot uk Do the obvious with at and dot -- Regards Roger Govier "Helmut" wrote in message ... Using F8 I see nothing happening on the window at all for lc nor for lr "Roger Govier" wrote: Hi Helmut It sorted fine on the sample of data I used to test it. Were you on the correct sheet when you ran the macro? What happens if you step through the macro with F8? What do you see in the immediate window for lr and lc? -- Regards Roger Govier "Helmut" wrote in message ... Hi roger, your suggestion runs without error but does NOTactually sort. Any further suggestions? "Roger Govier" wrote: Hi Helmut Try Sub test() Dim lr As Long, lc As Long lc = ActiveSheet.Cells.Find("*", [A11], , , xlByColumns, xlPrevious).Column lr = ActiveSheet.Cells.Find("*", [A11], , , xlByRows, xlPrevious).Row Range("A11").Select ActiveSheet.Sort.SortFields.Clear ActiveSheet.Sort.SortFields.Add _ Key:=Range(Cells(11, 1), Cells(lr, 1)) _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveSheet.Sort .SetRange Range(Cells(11, 1), Cells(lr, lc)) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub -- Regards Roger Govier "Helmut" wrote in message ... I tried but the "usedrange" doesn't work...I changed the lines: Range("A11").Select Activesheet.Sort.SortFields.Clear Activesheet.Sort.SortFields.Add Key:=usedrange _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With Activesheet.Sort .SetRange usedrange .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With "carlo" wrote: instead of activeworkbook.worksheets(bla) you could use activesheet which would take the sheet that has been activated the last as reference. and if you want to sort the whole used area you could use usedrange as range. If that doesn't help, try to give some more information. Carlo On Jan 9, 4:41 pm, Helmut wrote: How can I make the following work on any worksheet and on whatever the new range might be "A11:Axxx" - "A11:Jxxx" Range("A11").Select ActiveWorkbook.Worksheets("JAN").Sort.SortFields.C lear ActiveWorkbook.Worksheets("JAN").Sort.SortFields.A dd Key:=Range("A11:A68") _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("JAN").Sort .SetRange Range("A10:J68") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sort WorkSheetName unknown
it looks like your usedrange hasn't been recognised (as it is still in
small letters) try: Range("A11").Select Activesheet.Sort.SortFields.Clear Activesheet.Sort.SortFields.Add Key:=activesheet.usedrange _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With Activesheet.Sort .SetRange usedrange .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With hth Carlo |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sort WorkSheetName unknown
File received, sorted and reply sent by email
The problem was entirely mine. With Helmut selecting Range("A11"), I had also in advertently typed A11 instead of A1 into the calculation of lastrow and lastcolumn It should of course had read lc = ActiveSheet.Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column lr = ActiveSheet.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row -- Regards Roger Govier "Roger Govier" <roger@technology4unospamdotcodotuk wrote in message ... Hi Helmut if you want, you can email the file to me and I will take a look. to send direct mail to roger at technology4u dot co dot uk Do the obvious with at and dot -- Regards Roger Govier "Helmut" wrote in message ... Using F8 I see nothing happening on the window at all for lc nor for lr "Roger Govier" wrote: Hi Helmut It sorted fine on the sample of data I used to test it. Were you on the correct sheet when you ran the macro? What happens if you step through the macro with F8? What do you see in the immediate window for lr and lc? -- Regards Roger Govier "Helmut" wrote in message ... Hi roger, your suggestion runs without error but does NOTactually sort. Any further suggestions? "Roger Govier" wrote: Hi Helmut Try Sub test() Dim lr As Long, lc As Long lc = ActiveSheet.Cells.Find("*", [A11], , , xlByColumns, xlPrevious).Column lr = ActiveSheet.Cells.Find("*", [A11], , , xlByRows, xlPrevious).Row Range("A11").Select ActiveSheet.Sort.SortFields.Clear ActiveSheet.Sort.SortFields.Add _ Key:=Range(Cells(11, 1), Cells(lr, 1)) _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveSheet.Sort .SetRange Range(Cells(11, 1), Cells(lr, lc)) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub -- Regards Roger Govier "Helmut" wrote in message ... I tried but the "usedrange" doesn't work...I changed the lines: Range("A11").Select Activesheet.Sort.SortFields.Clear Activesheet.Sort.SortFields.Add Key:=usedrange _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With Activesheet.Sort .SetRange usedrange .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With "carlo" wrote: instead of activeworkbook.worksheets(bla) you could use activesheet which would take the sheet that has been activated the last as reference. and if you want to sort the whole used area you could use usedrange as range. If that doesn't help, try to give some more information. Carlo On Jan 9, 4:41 pm, Helmut wrote: How can I make the following work on any worksheet and on whatever the new range might be "A11:Axxx" - "A11:Jxxx" Range("A11").Select ActiveWorkbook.Worksheets("JAN").Sort.SortFields.C lear ActiveWorkbook.Worksheets("JAN").Sort.SortFields.A dd Key:=Range("A11:A68") _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("JAN").Sort .SetRange Range("A10:J68") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Finding a string of unknown length in a string of unknown length, Help! | Excel Discussion (Misc queries) | |||
| Copy from 1 worksheet to multipule worksheets based upon worksheetname | Excel Programming | |||
| HELP! Unknown range | Excel Worksheet Functions | |||
| Changing Worksheetname | Excel Discussion (Misc queries) | |||
| worksheetname in a cell | Excel Programming | |||
Linear Mode
