Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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

Copy multiple selected ranges from one worksheet

Copy multiple ranges from many worksheets/workbooks

Easily combine multiple selections from many worksheets/workbooks into single worksheet/workbook

It may be tedious to combine dozens of sheets from different workbooks into one sheet. But with Kutools for Excel’s Combine (worksheets and workbooks) utility, you can get it done with just several clicks! Full Feature Free Trial 30-day!

ad combine sheets books 1

Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
  • Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
  • More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
  • Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
  • Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
  • Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
  • Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
  • More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

arrow blue right bubble Copy multiple selected ranges with Clipboard

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 > doc copy multiple ranges 09 in the Clipboard group to display the Clipboard Pane.. See screenshot:

2. 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 Pane. see screenshot above:

3. Select the first cell of destination range you will paste all copied ranges to, and click Paste All button in the Clipboard Pane.

And then all of the copied ranges have been pasted into the specified destination range.


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. Holding the Ctrl key and select multiple nonadjacent ranges you want to use.

2. Press Alt + F11 keys at the same time to open the Microsoft Visual Basic for applications window, 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 the Run button to run the code.

4. And now please specify a cell to paste the ranges in the opening Copy Multiple Selection dialog box, and click the OK button. See screenshot:


arrow blue right bubble Quickly copy multiple selected ranges from one worksheet

The Copy Multiple Ranges utility of Kutools for Excel can help you easily copy multiple ranges from the active worksheet quickly. Please do as follows:

1. Select the ranges you want to use one by one without holding the Ctrl key, and then click Kutools > Copy Ranges. See screenshot:

2. In the Copy Multiple Ranges dialog box, check All option from Paste special section, and click the Ok button. See screenshot above:

Note: If you want to keep the row height and column width as the original ranges, please check Including row height option and Including column width option in the Copy Multiple Ranges dialog box.

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

And now 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 here for more information about Copy Multiple Ranges utility

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now


arrow blue right bubble Copy multiple ranges from many worksheets/workbooks

Kutools for Excel provides another Combine Worksheets utility to copy multiple ranges from many worksheets or many workbook easily in Excel. Please do as follows:

1. Click Enterprise > Combine.

2. In the opening Combine Worksheets – Step 1 of 3 dialog box, please check the Combine multiple worksheets from workbook into one worksheet option, and click the Next button.

3. In the Combine Worksheets – Step 2 of 3 dialog box, please do as below:

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now

(1) Select the workbook in the Workbook list section, and then click the Browse button behind each worksheet to specify the range from each worksheet;

(2) Click the Add button to add other workbooks which you will copy ranges from, and then repeat above (1) to specify ranges in added workbook.

(3) Click the Finish button.

4. Then a Kutools for excel dialog box comes out and asks for saving combination scenario. Please click the Yes button or No button as you need.

So far all specified ranges from multiple worksheets or workbooks have been copied and pasted into a new workbook.


arrow blue right bubbleDemo: Copy multiple selected ranges from one worksheet

arrow blue right bubbleDemo: Copy multiple selected ranges from many workbooks/worksheets

Kutools for Excel includes more than 300 handy tools for Excel, free to try without limitation in 30 days. Download and Free Trial Now!

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Peter Habershon · 1 years ago
    Hi there,
    Great code but wondered how I can paste values rather than just paste?
    Thanks :)
  • To post as a guest, your comment is unpublished.
    Peter Habershon · 1 years ago
    Hi there,
    Great post and the VBA code works perfectly. Is it possible to change the code to paste special > values? If so, how?
    Thanks!
  • To post as a guest, your comment is unpublished.
    Jacob Kring · 2 years ago
    Hi. Nice code, works great. Is it possible to modify the code in a way such that it is possible to insert/paste the market row/ranges multiple times instead of just one time?
  • To post as a guest, your comment is unpublished.
    Katrina Manahan · 2 years ago
    Hi! How do I use the same VBA Code above but instead of simply pasting, I would like it to paste special for just the values. The table I am putting the values (constants) into is already formatted and with totals (formulas)
    Thanks so much in advance!
    • To post as a guest, your comment is unpublished.
      · 2 years ago
      Hi Katrina Manahan,

      Please open the Microsoft Visual Basic for applications window, and create a new Module, just press CTRL + V to paste the VBA code directly. The sequence numbers before code won't be pasted.
  • To post as a guest, your comment is unpublished.
    Steve O · 3 years ago
    Thanks for the code, works perfectly. This odd lack of function within Excel has stumped me many times in the past. It's normally quicker in the end to work around it, but in this case I have 4000 individually colour-coded cells so any work-around would have taken a long time, so I'm very grateful.
    Steve
  • To post as a guest, your comment is unpublished.
    David Hedin · 3 years ago
    Hi,
    Is it possible to adjust the code so that the copied cells can be pasted in another sheet? Now I get the error message "400" when I attempt this. I use Office 2010.

    Also, is it possible to copy so that empty lines are deleted? I have a big document and I copy some cells with hundreds of non-copied rows between them. This makes for a rather bulky output.
  • To post as a guest, your comment is unpublished.
    Yunivor · 4 years ago
    I found that if you have the Clipboard task plane open you can copy multiple rows simply using ctrl+C and paste them in order with crtl+V using Excel 2007.
  • To post as a guest, your comment is unpublished.
    M · 4 years ago
    Thank you SOOOOOO much - what a great site
  • To post as a guest, your comment is unpublished.
    Lyn Mc · 5 years ago
    Found above explanation of options to copy multiple ranges very helpful - thanks! I used the 'clipboard' option to copy multiple rows. Had to select each group of consecutive rows and copy it, move on and select next row or group of consecutive rows and copy it, etc.. But after this its easy, go to where you want to paste them, eg a new sheet, and click 'Paste all' from the Clipboard and all the rows are copied to there with out any gaps! Exactly what I wanted - thanks again!
  • To post as a guest, your comment is unpublished.
    Abani Pattanayak · 5 years ago
    I want to select the rows like 1, 5,6,10. so how can i copy these rows ???
    • To post as a guest, your comment is unpublished.
      David Hedin · 3 years ago
      In this case you can just select the rows and copy them as usual.
      • To post as a guest, your comment is unpublished.
        K Raj · 3 years ago
        How can I copy cell a1, e5, g2, and so on.... (more than 1000 cells in same column) and paste them into b1, f5, h2 (right into the next column in same row.)