Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort column without referencing sheet name
I am using a macro to reformat a txt file which was exported from a
proprietary program and opened with Excel. The sort works fine, but I recorded the macro to get the code and when it sorts by column A, the recorded code references the specific worksheet name which was automatically named for the txt file name. Problem is that subsequent txt files will always have a different name. I just need the wording to make it for whatever the current sheet is. TIA, Kelly The recorded code came out like this: Columns("A:F").Select Application.CutCopyMode = False ActiveWorkbook.Worksheets("QSYSPRT732957").Sort.So rtFields.Clear ActiveWorkbook.Worksheets("QSYSPRT732957").Sort.So rtFields.Add Key:=Range( _ "A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortTextAsNumbers With ActiveWorkbook.Worksheets("QSYSPRT732957").Sort .SetRange Range("A1:F300") .Header = xlNo .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort column without referencing sheet name
Replace
Worksheets("QSYSPRT732957") with ActiveSheet If this post helps click Yes --------------- Jacob Skaria "KellyinCali" wrote: I am using a macro to reformat a txt file which was exported from a proprietary program and opened with Excel. The sort works fine, but I recorded the macro to get the code and when it sorts by column A, the recorded code references the specific worksheet name which was automatically named for the txt file name. Problem is that subsequent txt files will always have a different name. I just need the wording to make it for whatever the current sheet is. TIA, Kelly The recorded code came out like this: Columns("A:F").Select Application.CutCopyMode = False ActiveWorkbook.Worksheets("QSYSPRT732957").Sort.So rtFields.Clear ActiveWorkbook.Worksheets("QSYSPRT732957").Sort.So rtFields.Add Key:=Range( _ "A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortTextAsNumbers With ActiveWorkbook.Worksheets("QSYSPRT732957").Sort .SetRange Range("A1:F300") .Header = xlNo .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort column without referencing sheet name
Would this work?
Cells.Select Selection.Sort Key1:=Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, Header:=xlNo, MatchCase:=False, Orientation:=xlTopToBottom "KellyinCali" wrote: I am using a macro to reformat a txt file which was exported from a proprietary program and opened with Excel. The sort works fine, but I recorded the macro to get the code and when it sorts by column A, the recorded code references the specific worksheet name which was automatically named for the txt file name. Problem is that subsequent txt files will always have a different name. I just need the wording to make it for whatever the current sheet is. TIA, Kelly The recorded code came out like this: Columns("A:F").Select Application.CutCopyMode = False ActiveWorkbook.Worksheets("QSYSPRT732957").Sort.So rtFields.Clear ActiveWorkbook.Worksheets("QSYSPRT732957").Sort.So rtFields.Add Key:=Range( _ "A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortTextAsNumbers With ActiveWorkbook.Worksheets("QSYSPRT732957").Sort .SetRange Range("A1:F300") .Header = xlNo .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort column without referencing sheet name
Worked... of course! I knew it would be incredibly simple I just didn't know
the syntax. Thanks for indulging my laziness!! Thanks Jacob! "Jacob Skaria" wrote: Replace Worksheets("QSYSPRT732957") with ActiveSheet If this post helps click Yes --------------- Jacob Skaria "KellyinCali" wrote: I am using a macro to reformat a txt file which was exported from a proprietary program and opened with Excel. The sort works fine, but I recorded the macro to get the code and when it sorts by column A, the recorded code references the specific worksheet name which was automatically named for the txt file name. Problem is that subsequent txt files will always have a different name. I just need the wording to make it for whatever the current sheet is. TIA, Kelly The recorded code came out like this: Columns("A:F").Select Application.CutCopyMode = False ActiveWorkbook.Worksheets("QSYSPRT732957").Sort.So rtFields.Clear ActiveWorkbook.Worksheets("QSYSPRT732957").Sort.So rtFields.Add Key:=Range( _ "A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortTextAsNumbers With ActiveWorkbook.Worksheets("QSYSPRT732957").Sort .SetRange Range("A1:F300") .Header = xlNo .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort column without referencing sheet name
Try..
Cells.Sort Key1:=Range("A1"), Order1:=xlAscending, _ Header:=xlNo, Orientation:=xlTopToBottom The Sort method expects the below (all of them are optional) expression.Sort(Key1, Order1, Key2, Type, Order2, Key3, Order3, Header, OrderCustom, MatchCase, Orientation, SortMethod, DataOption1, DataOption2, DataOption3) If this post helps click Yes --------------- Jacob Skaria "KellyinCali" wrote: Would this work? Cells.Select Selection.Sort Key1:=Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, Header:=xlNo, MatchCase:=False, Orientation:=xlTopToBottom "KellyinCali" wrote: I am using a macro to reformat a txt file which was exported from a proprietary program and opened with Excel. The sort works fine, but I recorded the macro to get the code and when it sorts by column A, the recorded code references the specific worksheet name which was automatically named for the txt file name. Problem is that subsequent txt files will always have a different name. I just need the wording to make it for whatever the current sheet is. TIA, Kelly The recorded code came out like this: Columns("A:F").Select Application.CutCopyMode = False ActiveWorkbook.Worksheets("QSYSPRT732957").Sort.So rtFields.Clear ActiveWorkbook.Worksheets("QSYSPRT732957").Sort.So rtFields.Add Key:=Range( _ "A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortTextAsNumbers With ActiveWorkbook.Worksheets("QSYSPRT732957").Sort .SetRange Range("A1:F300") .Header = xlNo .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing entire column of data from another sheet. | Excel Worksheet Functions | |||
Sort List, Create Sheet for each unique item in column, move data | Excel Programming | |||
Referencing date column A & time column B to get info from column | Excel Discussion (Misc queries) | |||
Button To Sort Sheet Contents On Column? | Excel Programming | |||
Sort referencing incorrect worksheet | Excel Programming |