Hi,

Am Sun, 12 Nov 2017 11:46:48 -0800 (PST) schrieb :

A24:A53 == copies and pastes it to the "Position Control" spreadsheet into A9:A38
C24:C53 == copies and pastes it to the "Position Control" spreadsheet into C9:C38
F24:F53 == copies and pastes it to the "Position Control" spreadsheet into E9:E38
H24:H53 == copies and pastes it to the "Position Control" spreadsheet into G9:G38
K24:K53 == copies and pastes it to the "Position Control" spreadsheet into I9:I38
M24:M53 == copies and pastes it to the "Position Control" spreadsheet into K9:K38
P24:P53 == copies and pastes it to the "Position Control" spreadsheet into M9:M38
R24:R53 == copies and pastes it to the "Position Control" spreadsheet into O9:O38
U24:U53 == copies and pastes it to the "Position Control" spreadsheet into Q9:Q38
W24:W53 == copies and pastes it to the "Position Control" spreadsheet into S9:S38

I would then like the names in each of the "Position Control" ranges to be sorted alphabetically. Unfortunately, the users entering names on the "Input" spreadsheet have been known to delete names by pressing the delete key or simply entering a blank over the name. The Lastname, Firstname's may not be contiguous. The sort should take this into account and still show names at the top of the sorted column in the "Position Control" spreadsheet.

try:

Sub Test()
Dim rng1 As Range, rng2 As Range
Dim ar As Range, rngC As Range
Dim i As Integer

Set rng1 = Sheets("Input").Range("A24:A53,C24:C53,F24:F53," _
& "H24:H53,K24:K53,M24:M53,P24:P53,R24:R53,U24:U53,W 24:W53")

Set rng2 = Sheets("Position Control").Range("A9:A38,C9:C38,E9:E38," _
& "G9:G38,I9:I38,K9:K38,M9:M38,O9:O39,Q9:Q38,S9:S38" )

For i = 1 To rng1.Areas.Count
With rng2.Areas(i)
.Value = rng1.Areas(i).Value
.Sort key1:=.Cells(1), order1:=xlAscending, Header:=xlNo
End With
Next
End Sub


Regards
Claus B.
--
Windows10
Office 2016