Hi,
I created a spreadsheet called "Input" where users will be entering Lastname, Firstname type information into the following ranges:
A24:A53
C24:C53
F24:F53
H24:H53
K24:K53
M24:M53
P24:P53
R24:R53
U24:U53
W24:W53

Sample data looks like this:

Hendricks, Ted
Bond, Sally
Newman, Olivia
Hodges, Faith
Vaughan, Steven
Hudson, Yvonne
Burgess, Jessica

There will be a variable number of names in each range. In other words although there are cells for 29 names, there will be a smaller number of names in each range. One range may have 12 names, another 5, and so on.


I want a macro so that when the user clicks a macro button on the "Input" spreadsheet, it copies the information from the cell ranges mentioned above and pastes it into a spreadsheet called "Position Control" and into the following cells on the "Position Control" spreadsheet. Below is where each range of data should be copied.

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.

Another way of saying this is:
Names may look this way in the Input spreadsheet.
**************
Haywood, Lucas
Coleman, Olivia
Dowd, Jasmine
Welch, Liam

King, Chloe
************

But they should appear this way in the Position Control spreadsheet.
************
Coleman, Olivia
Dowd, Jasmine
Haywood, Lucas
King, Chloe
Welch, Liam
*************





Thanks very much for your help with this.

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

Thanks Claus,
This worked spectacularly. Have a great week !!

Outstanding!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion