Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 12th 17, 08:46 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2012
Posts: 14
Default Copying and sorting with some twists

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.



  #2   Report Post  
Old November 13th 17, 03:14 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,536
Default Copying and sorting with some twists

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
  #3   Report Post  
Old November 13th 17, 08:49 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2012
Posts: 14
Default Copying and sorting with some twists

Thanks Claus,
This worked spectacularly. Have a great week !!
  #4   Report Post  
Old November 13th 17, 10:14 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 763
Default Copying and sorting with some twists

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Create a new Worksheet with a few Twists Sean Excel Programming 6 November 27th 07 08:28 PM
sorting and copying Stan Halls Excel Worksheet Functions 3 August 2nd 07 07:02 AM
Auto Close A Workbook...With Two Twists! jwleonard[_5_] Excel Programming 0 September 18th 04 09:49 PM
Auto Close A Workbook...With Two Twists! jwleonard[_4_] Excel Programming 0 September 18th 04 08:48 AM
Auto Close A Workbook...With Two Twists! jwleonard[_3_] Excel Programming 0 September 17th 04 07:29 AM


All times are GMT +1. The time now is 02:52 PM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017