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:-
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?
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:-
VBA Code:
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?