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

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

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

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

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