How to copy multiple selections or ranges in Excel?

For example, you have a large worksheet, and now you only want to copy some selected ranges and distribute to other worksheets. But when you select the multiple ranges and click copy, there will be a prompt dialog box to remind you “That command cannot be used on multiple selections.” In this condition, how do you quickly copy multiple selected ranges? Here are some tricks for you to solve this task.

Copy multiple selected ranges with Clipboard

Copy multiple selected ranges with VBA code

Quickly copy multiple selected ranges with Kutools for Excel

Kutools for Excel: add 120 new features in Excel. Save one hour every day.
Classic Menu for Office: brings back classic menus to Office 2010 and 2013 (includes Office 365).
Office Tab: brings tabbed interface to Office as the use of web browser Chrome, Firefox and Internet Explorer.

arrow blue right bubble Copy multiple selected ranges with Clipboard

Hot
Amazing! Using Tabs in Excel like Firefox, Chrome, Internet Explore 10!

With the help of Clipboard, you can paste all of the selected ranges to another range or worksheet at a time. Please do as follows:

1. Click Home, and then click doc-copy-multiple-ranges4 button in the Clipboard group. See screenshot:

doc-copy-multiple-ranges1

2. Then the Clipboard Task Pane will display.

3. And then copy the selected ranges that you need one by one. At the same time, the copied ranges have been appeared in the Clipboard Task Pane. see screenshot:

doc-copy-multiple-ranges2

4. Then open a new worksheet or at the active worksheet, select one cell you would like to paste the selected ranges. And click Paste All from the Clipboard Task Pane. And all of the copied ranges have been pasted into the specified location.


arrow blue right bubble Copy multiple selected ranges with VBA code

With VBA code, you can also copy multiple selected ranges and paste them into any other worksheet.

1. Press Ctrl key and select the ranges you want to use.

2. Click Developer > Visual Basic, a new Microsoft Visual Basic for applications window will be displayed, click Insert > Module, and input the following code into the Module:

Option Explicit
Sub CopyMultipleSelection()
Dim SelAreas() As Range
Dim PasteRange As Range
Dim UpperLeft As Range
Dim NumAreas As Integer, i As Integer
Dim TopRow As Long, LeftCol As Integer
Dim RowOffset As Long, ColOffset As Integer
Dim NonEmptyCellCount As Integer
' Exit if a range is not selected
If TypeName(Selection) < > "Range" Then
MsgBox "Select the range to be copied. A multiple selection is allowed."
Exit Sub
End If
' Store the areas as separate Range objects
NumAreas = Selection.Areas.Count
ReDim SelAreas(1 To NumAreas)
For i = 1 To NumAreas
Set SelAreas(i) = Selection.Areas(i)
Next
' Determine the upper left cell in the multiple selection
TopRow = ActiveSheet.Rows.Count
LeftCol = ActiveSheet.Columns.Count
For i = 1 To NumAreas
If SelAreas(i).Row < TopRow Then TopRow = SelAreas(i).Row
If SelAreas(i).Column < LeftCol Then LeftCol = SelAreas(i).Column
Next
Set UpperLeft = Cells(TopRow, LeftCol)
' Get the paste address
On Error Resume Next
Set PasteRange = Application.InputBox _
(Prompt:="Specify the upper left cell for the paste range:", _
Title:="Copy Mutliple Selection", _
Type:=8)
On Error GoTo 0
' Exit if canceled
If TypeName(PasteRange) < > "Range" Then Exit Sub
' Make sure only the upper left cell is used
Set PasteRange = PasteRange.Range("A1")
' Check paste range for existing data
NonEmptyCellCount = 0
For i = 1 To NumAreas
RowOffset = SelAreas(i).Row - TopRow
ColOffset = SelAreas(i).Column - LeftCol
NonEmptyCellCount = NonEmptyCellCount + _
Application.CountA(Range(PasteRange.Offset(RowOffset, ColOffset), _
PasteRange.Offset(RowOffset + SelAreas(i).Rows.Count - 1, _
ColOffset + SelAreas(i).Columns.Count - 1)))
Next i
' If paste range is not empty, warn user
If NonEmptyCellCount < > 0 Then _
If MsgBox("Overwrite existing data?", vbQuestion + vbYesNo, _
"Copy Multiple Selection") < > vbYes Then Exit Sub
' Copy and paste each area
For i = 1 To NumAreas
RowOffset = SelAreas(i).Row - TopRow
ColOffset = SelAreas(i).Column - LeftCol
SelAreas(i).Copy PasteRange.Offset(RowOffset, ColOffset)
Next i
End Sub

3. Then click  doc-multiply-calculation-3 button to run the code. And a Copy Multiple Selection prompt box will pop out to remind you to specify a cell to paste the ranges. See screenshot:

doc-copy-multiple-ranges3

4. Click OK. And all of selected ranges have been pasted into a specific cell.

arrow blue right bubble Quickly copy multiple selected ranges with Kutools for Excel

Copy Multiple Ranges of Kutools for Excel is a helpful tool to quickly and easily deal with this task.

Kutools for Excel: with more than 120 handy Excel add-ins, free to try with no limitation in 30 days. Get it Now

If you have installed Kutools for Excel, please do as the following steps:

1. Hold down the Ctrl key and select the ranges you want to use one by one. And click Kutools > Copy Ranges…, see screenshot:

doc-copy-multiple-ranges7

2. In the Copy Multiple Ranges dialog box, check All option from Paste special. (if you want to keep the row height and column width as the original ranges, please check Including row height and Including column width. ) see screenshot:

doc-copy-multiple-ranges5

3. And then click OK. And specify a cell to paste the ranges in the following prompt box.

doc-copy-multiple-ranges6

4. Then click OK, all of the selected ranges will be pasted into the specified cell as the same row height and column width as the original selections.

Click to know more about theCopy Multiple Ranges feature.


Kutools for Excel

More than 120 Advanced Functions for Excel 2013, 2010, 2007 and Office 365.

screen shot

btn read more     btn download     btn purchase

Add comment


Security code
Refresh