Hi everyone
So I have been working on my spreadsheet for some time and decided to update buy adding buttons.
I do a lot of sorts with my data, 1 sort in particular uses 10 lines to edit to create the sort I need and everything is working ok.
I decided to assign each seperate sort to a button which is going to save me stupid amounts of time, I have 5 buttons done so far, each of them as expected and I have a few more buttons to add.
Here is the question.
I always run the sort before inserting a button, then when it comes to running the Macro, I click data>sort, all my sort criteria are already in place so click ok, click on any cell to remove the selection and stop Macro.
The I have to edit the Macro because its going to contain the range I used for the sort but as the range is different every time, I remove any ranges in the VBA and replace with "selection."
This doesnt always work so on 2 of my sort Macros Ive changed a range to selection and left other ranges as they are and the Buttons do work but Im not happy the Macro still uses a defined range and I dont want it to get messed up because of this if theres a way to remove the range, Im not really up on VBA coding but Im learning since I'vw been here.
Here is one of Macros which I've labelled in Red.
I use Excel 2019
Many thanks
Sub Results_by_K_Sort()
'
' Results_by_K_Sort Macro
'
'
Selection.Select This used to contain a range
Selection.Activate This used to contain a range
ActiveWorkbook.Worksheets("FIX").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("FIX").Sort.SortFields.Add(Range("K3:K19"), _ Could this range cause probems?
xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, _
255, 0)
ActiveWorkbook.Worksheets("FIX").Sort.SortFields.Add(Range("K3:K19"), _ Could this range cause probems?
xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(146, _
208, 80)
ActiveWorkbook.Worksheets("FIX").Sort.SortFields.Add(Range("K3:K19"), _ Could this range cause probems?
xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, _
192, 0)
ActiveWorkbook.Worksheets("FIX").Sort.SortFields.Add(Range("K3:K19"), _ Could this range cause probems?
xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(0, 176 _
, 240)
ActiveWorkbook.Worksheets("FIX").Sort.SortFields.Add2 Key:=Range("K3:K19"), _ Could this range cause probems?
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("FIX").Sort
.SetRange Selection This used to contain a range
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("B2").Select
End Sub
So I have been working on my spreadsheet for some time and decided to update buy adding buttons.
I do a lot of sorts with my data, 1 sort in particular uses 10 lines to edit to create the sort I need and everything is working ok.
I decided to assign each seperate sort to a button which is going to save me stupid amounts of time, I have 5 buttons done so far, each of them as expected and I have a few more buttons to add.
Here is the question.
I always run the sort before inserting a button, then when it comes to running the Macro, I click data>sort, all my sort criteria are already in place so click ok, click on any cell to remove the selection and stop Macro.
The I have to edit the Macro because its going to contain the range I used for the sort but as the range is different every time, I remove any ranges in the VBA and replace with "selection."
This doesnt always work so on 2 of my sort Macros Ive changed a range to selection and left other ranges as they are and the Buttons do work but Im not happy the Macro still uses a defined range and I dont want it to get messed up because of this if theres a way to remove the range, Im not really up on VBA coding but Im learning since I'vw been here.
Here is one of Macros which I've labelled in Red.
I use Excel 2019
Many thanks
Sub Results_by_K_Sort()
'
' Results_by_K_Sort Macro
'
'
Selection.Select This used to contain a range
Selection.Activate This used to contain a range
ActiveWorkbook.Worksheets("FIX").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("FIX").Sort.SortFields.Add(Range("K3:K19"), _ Could this range cause probems?
xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, _
255, 0)
ActiveWorkbook.Worksheets("FIX").Sort.SortFields.Add(Range("K3:K19"), _ Could this range cause probems?
xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(146, _
208, 80)
ActiveWorkbook.Worksheets("FIX").Sort.SortFields.Add(Range("K3:K19"), _ Could this range cause probems?
xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, _
192, 0)
ActiveWorkbook.Worksheets("FIX").Sort.SortFields.Add(Range("K3:K19"), _ Could this range cause probems?
xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(0, 176 _
, 240)
ActiveWorkbook.Worksheets("FIX").Sort.SortFields.Add2 Key:=Range("K3:K19"), _ Could this range cause probems?
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("FIX").Sort
.SetRange Selection This used to contain a range
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("B2").Select
End Sub
