Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting - should have been easy
I have a worksheet open, so it is the active worksheet and did some stuff,
ending with 27 rows (number of rows will vary each day, but should get above 35 to 40), A-E. Need to sort from A5 down. Started at A1. Recording Macro said: ActiveCell.Offset(4, 0).Range("A1:E31").Select ActiveWorkbook.Worksheets("prysm06-17-09").Sort.SortFields.Clear ActiveWorkbook.Worksheets("prysm06-17-09").Sort.SortFields.Add Key:= _ ActiveCell.Range("A1:A23"), SortOn:=xlSortOnValues, Order:=xlAscending, _ DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("prysm06-17-09").Sort .SetRange ActiveCell.Range("A1:E23") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With I want to use this on a different file each day, so name wil be different. How do I get past ActiveWorkbook.Worksheets("prysm06-17-09"). and just sort on the sheet that open, with out naming the file each time. My syntax sucks.... :-) Thanks, Ron |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting - should have been easy
Guess I wasn't patient enough. Changed it to ActiveWorkbook.ActiveSheet.Sort
and it worked. Thanks anyway. "Ron" wrote: I have a worksheet open, so it is the active worksheet and did some stuff, ending with 27 rows (number of rows will vary each day, but should get above 35 to 40), A-E. Need to sort from A5 down. Started at A1. Recording Macro said: ActiveCell.Offset(4, 0).Range("A1:E31").Select ActiveWorkbook.Worksheets("prysm06-17-09").Sort.SortFields.Clear ActiveWorkbook.Worksheets("prysm06-17-09").Sort.SortFields.Add Key:= _ ActiveCell.Range("A1:A23"), SortOn:=xlSortOnValues, Order:=xlAscending, _ DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("prysm06-17-09").Sort .SetRange ActiveCell.Range("A1:E23") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With I want to use this on a different file each day, so name wil be different. How do I get past ActiveWorkbook.Worksheets("prysm06-17-09"). and just sort on the sheet that open, with out naming the file each time. My syntax sucks.... :-) Thanks, Ron |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting - should have been easy
Option Explicit
Sub test() Dim source As Range With ActiveSheet Set source = .Range(.Range("A5"), .Range("E5").End(xlDown)) End With source.Sort source.Range("A1"), xlAscending End Sub "Ron" wrote in message ... Guess I wasn't patient enough. Changed it to ActiveWorkbook.ActiveSheet.Sort and it worked. Thanks anyway. "Ron" wrote: I have a worksheet open, so it is the active worksheet and did some stuff, ending with 27 rows (number of rows will vary each day, but should get above 35 to 40), A-E. Need to sort from A5 down. Started at A1. Recording Macro said: ActiveCell.Offset(4, 0).Range("A1:E31").Select ActiveWorkbook.Worksheets("prysm06-17-09").Sort.SortFields.Clear ActiveWorkbook.Worksheets("prysm06-17-09").Sort.SortFields.Add Key:= _ ActiveCell.Range("A1:A23"), SortOn:=xlSortOnValues, Order:=xlAscending, _ DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("prysm06-17-09").Sort .SetRange ActiveCell.Range("A1:E23") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With I want to use this on a different file each day, so name wil be different. How do I get past ActiveWorkbook.Worksheets("prysm06-17-09"). and just sort on the sheet that open, with out naming the file each time. My syntax sucks.... :-) Thanks, Ron |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting - should have been easy
Thanks, Patrick...the more I do in Excel, the more I know how much I can't
do..... Ron "Patrick Molloy" wrote: Option Explicit Sub test() Dim source As Range With ActiveSheet Set source = .Range(.Range("A5"), .Range("E5").End(xlDown)) End With source.Sort source.Range("A1"), xlAscending End Sub "Ron" wrote in message ... Guess I wasn't patient enough. Changed it to ActiveWorkbook.ActiveSheet.Sort and it worked. Thanks anyway. "Ron" wrote: I have a worksheet open, so it is the active worksheet and did some stuff, ending with 27 rows (number of rows will vary each day, but should get above 35 to 40), A-E. Need to sort from A5 down. Started at A1. Recording Macro said: ActiveCell.Offset(4, 0).Range("A1:E31").Select ActiveWorkbook.Worksheets("prysm06-17-09").Sort.SortFields.Clear ActiveWorkbook.Worksheets("prysm06-17-09").Sort.SortFields.Add Key:= _ ActiveCell.Range("A1:A23"), SortOn:=xlSortOnValues, Order:=xlAscending, _ DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("prysm06-17-09").Sort .SetRange ActiveCell.Range("A1:E23") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With I want to use this on a different file each day, so name wil be different. How do I get past ActiveWorkbook.Worksheets("prysm06-17-09"). and just sort on the sheet that open, with out naming the file each time. My syntax sucks.... :-) Thanks, Ron |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting - should have been easy
That worked so much better. Thanks Patrick Ron "Patrick Molloy" wrote: Option Explicit Sub test() Dim source As Range With ActiveSheet Set source = .Range(.Range("A5"), .Range("E5").End(xlDown)) End With source.Sort source.Range("A1"), xlAscending End Sub "Ron" wrote in message ... Guess I wasn't patient enough. Changed it to ActiveWorkbook.ActiveSheet.Sort and it worked. Thanks anyway. "Ron" wrote: I have a worksheet open, so it is the active worksheet and did some stuff, ending with 27 rows (number of rows will vary each day, but should get above 35 to 40), A-E. Need to sort from A5 down. Started at A1. Recording Macro said: ActiveCell.Offset(4, 0).Range("A1:E31").Select ActiveWorkbook.Worksheets("prysm06-17-09").Sort.SortFields.Clear ActiveWorkbook.Worksheets("prysm06-17-09").Sort.SortFields.Add Key:= _ ActiveCell.Range("A1:A23"), SortOn:=xlSortOnValues, Order:=xlAscending, _ DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("prysm06-17-09").Sort .SetRange ActiveCell.Range("A1:E23") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With I want to use this on a different file each day, so name wil be different. How do I get past ActiveWorkbook.Worksheets("prysm06-17-09"). and just sort on the sheet that open, with out naming the file each time. My syntax sucks.... :-) Thanks, Ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Another sorting question - easy one! | New Users to Excel | |||
Easy Sorting for Senior mgt | Excel Programming | |||
Pivot table sorting, should be easy I think | Excel Discussion (Misc queries) | |||
Easy Sorting Question | Excel Discussion (Misc queries) | |||
How do I group multiple lines of excel info for easy sorting | New Users to Excel |