Copy-Paste Macro for Non Contiguous Ranges

njpamdnc

New Member
Joined
Feb 16, 2019
Messages
34
Office Version
  1. 2007
Platform
  1. Windows
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)

copy-paste con contiguous ranges macro.png
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

t0ny84

Board Regular
Joined
Jul 6, 2020
Messages
137
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
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.

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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
another one for you to try
- your 5 ranges are one contiguous range A1:E200

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

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
@t0ny84
Values in columns B:E relate to names in column A
Your code sorts every column separately which rmixes up the data
 

Watch MrExcel Video

Forum statistics

Threads
1,129,296
Messages
5,635,365
Members
416,856
Latest member
silentir

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top