Create a Dynamic Sort Range in a Macro.

[Kayslover]

Hi All,

I have a macro that creates individual sheets from a list, it then creates a Summary list.

Prior to running, the Macro, the data needs to be copied and Past Special Values over itself and then needs to be sorted to enable the split to work.

Currently, the copy Paste Special and Sort are undertaken manually. The following is the macro:-

Sub PasteSpecial_Sort()
' This Macro Selects the Data range, copy’s the range and Past Special Values over itself. It then Sorts the data range using Column D as the sort Key.
Range("A2:T2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

' This area is the Sort Part

ActiveWorkbook.Worksheets("Buylist").Sort.SortFields.Clear

ActiveWorkbook.Worksheets("Buylist").Sort.SortFields.Add Key:=Range("D3:D218"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("Buylist").Sort

.SetRange Range("A2:T218")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply

End With

End Sub

The problem with the macro is that the range will differ every year (I selected the range by holding the Shift+CTRL+Arrow Down.

Can the above be adapted to create the range using the Shift+CTRL+Arrow Down automatically?

 

[Fluff]

You're welcome & thanks for the feedback.