Copy-Paste Macro for Non Contiguous Ranges

[njpamdnc]

Hello...My name is Robert, and I am requesting assistance to create a macro that will allow me to copy 5 non contiguous equally-sized ranges from Column A, Column B, Column C, Column D and Column E along with pasting them to Column F, Column G, Column H, Column I and Column J in one large cluster. Furthermore, I would like the data to be automatically alphabetized by student name.

What you are seeing in the attached image is an example of my actual 5 classes of students. I selected and copied each section of the data on the left side, and pasted it on the right side, I also manually sorted the target data in alphabetical order, but I am trying to reduce as many manual steps as possible during this process. Each class has blank cells towards the bottom of its list to accommodate new students. I simply don't want the blank cells mixed-in with the non blank cells after the target data is made. The ranges for my actual classes are as follows:

Class 1(A1:E40)
Class 2(A41:E80)
Class 3(A81:E120)
Class 4(A121:E160)
Class 5(A161:E200)

 

[t0ny84]

Hi njpamdnc,

The below code should copy the columns then sort them A to Z, let me know if you run in to any issues and I will go through it when I am back at my computer.
You can assign this macro to a button on the page.

Sub copydata()
' Column A - Column F
Range("A:A").Copy
Range("F:F").Insert
Range("F:F").Sort Key1:=Range("F1"), Order1:=xlAscending, Header:=xlNo

' Column B - Column G
Range("B:B").Copy
Range("G:G").Insert
Range("G:G").Sort Key1:=Range("G1"), Order1:=xlAscending, Header:=xlNo

' Column C - Column H
Range("C:C").Copy
Range("H:H").Insert
Range("H:H").Sort Key1:=Range("H1"), Order1:=xlAscending, Header:=xlNo

' Column D - Column I 
Range("D:D").Copy
Range("I:I").Insert
Range("I:I").Sort Key1:=Range("I1"), Order1:=xlAscending, Header:=xlNo

' Column E - Column J 
Range("E:E").Copy
Range("J:J").Insert
Range("J:J").Sort Key1:=Range("J1"), Order1:=xlAscending, Header:=xlNo
End Sub

 

[Yongle]

another one for you to try
- your 5 ranges are one contiguous range A1:E200

Sub CopySort()
    Application.CutCopyMode = False
    With ActiveSheet
        .Range("F1:J200").Value = .Range("A1:E200").Value
        .Sort.SortFields.Clear
        .Range("F1:J200").Sort Key1:=.Range("F1"), Order1:=xlAscending, Header:=xlNo
    End With
End Sub

 

[Yongle]

@t0ny84
Values in columns B:E relate to names in column A
Your code sorts every column separately which rmixes up the data