Skip to main content

Kutools for Office — One Suite. Five Tools. Get More Done.

How to copy multiple selections or ranges in Excel?

Author Xiaoyang Last modified

In day-to-day Excel work, it's common to need portions of data spread throughout a large worksheet rather than contiguous blocks. For example, you may want to copy specific tables, columns, or non-adjacent cells and gather or transfer them all at once to another worksheet. However, when you select several non-contiguous ranges and attempt to copy them together, Excel displays an alert: “That command cannot be used on multiple selections.” This can be frustrating and makes distributing or compiling separate pieces of data cumbersome if using default Excel features. So, what effective methods are available to quickly copy multiple selected ranges? This guide will introduce several practical solutions, analyzing each method's value, applicable scenarios, and things to watch out for to help you handle these common spreadsheet requirements more efficiently.


Copy multiple selected ranges with Clipboard

Excel’s built-in Clipboard is often overlooked, but it allows you to aggregate several content snippets across your worksheet. With the Clipboard Pane, you can copy content from different locations one by one, collect them, and then paste everything together into your chosen area on the destination worksheet. This workaround is best for scenarios where visual content, shapes, or smaller selections need gathering, but keep in mind that Excel treats these as individual copy actions—so formatting and structure might not always remain consistent when pasting. Here are the detailed steps and important points to remember:

1. Click Home > a screenshot of the launch icon in the Clipboard group in the Clipboard group to show the Clipboard Pane. (This is usually found at the lower-right of the Clipboard group in Excel’s ribbon.) See screenshot:
a screenshot showing how to open the Clipboard pane in Exel

2. Copy each range you need, one by one. After each copy, the selected content will appear as a unique item in the Clipboard Pane on the left. Repeat this process for all ranges you wish to combine. You can review the Clipboard list to ensure all needed ranges have been captured and remove any unintended copies before pasting.

3. Go to the worksheet and select the destination cell where you want to aggregate the copied ranges. In the Clipboard Pane, click Paste All to paste every collected item at once.

Pasting via the Clipboard will stack content vertically by default; so, if the data is meant to align in a certain structure, you may need to adjust pasted results, especially for tables or lists. Also, the Clipboard retains up to 24 items—if you exceed this, the oldest items will be replaced. Always verify your paste area is clear to avoid overwriting important data inadvertently.

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

If you need to gather or merge content from numerous sheets or workbooks, repeating manual copy-paste across workbooks can be tedious, error-prone, and time-consuming—especially for large-scale data aggregation. With Kutools for Excel’s Combine (worksheets and workbooks) utility, you can integrate multiple sheets and workbooks into a single destination efficiently, minimizing manual effort and maintaining data structure throughout the process. This is ideal for monthly reports, cross-departmental data summaries, or consolidating historical data.


a screenshot of easily ombine multiple selections from many worksheets/workbooks into single worksheet/workbook with Kutools for Excel

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now

Copy multiple selected ranges with VBA code

If your workflow regularly involves copying multiple separated ranges, automating the process via VBA (Visual Basic for Applications) can save significant time and reduce human error. This solution is best for advanced users or when repetitive, structured tasks are required—such as copying the same sections each week or reformatting data for reporting. This method offers increased flexibility but requires careful step-by-step operation and attention for paste locations to avoid accidental data overwrite.

1. Hold down the Ctrl key and use your mouse to select each non-adjacent range you want to gather.

2. Press Alt + F11 together to open the Microsoft Visual Basic for Applications (VBA) editor window, then click Insert > Module. Paste the following code directly into the module window:

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. In the VBA window, click the Run button (or press F5) to execute the code.

4. An input box will appear prompting you to specify the top-left cell where you want to paste your combined ranges. Click OK after confirming.
a screenshot of selecting a destination cell to place the copied ranges

Precautions and troubleshooting: This VBA code arranges copied selections starting from the top-left of your designated destination. Watch out for potential data overwrite—if the destination area is not empty, a warning will display. If you encounter any “Subscript out of range” or error messages, check that your selection consists entirely of cell ranges. This approach doesn’t support copying non-cell items, such as shapes or charts, and will only copy values and cell formatting, not objects.


Quickly copy multiple selected ranges from one worksheet

Kutools for Excel provides a more efficient solution with its Copy Ranges tool: it lets you select all the ranges you need in a worksheet and then copy them in one step. In a single, easy-to-use interface, you can choose exactly how the copied content should be pasted—whether to paste all contents, formulas only, values only, formats only, etc. You can also optionally retain row heights and column widths, saving time and ensuring consistent formatting.

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now

1. Select the ranges one by one by holding the Ctrl key—then go to Kutools > Copy Ranges. See screenshot:
a screenshot of enabling and configuring the Copy Multiple Ranges feature of Kutools for Excel

2. In the Copy Multiple Ranges dialog box, choose All from the Paste special options and confirm by clicking OK. You can further customize the paste settings here—such as including only values, or all formatting.

Note: To maintain the original row heights and column widths for all ranges, please also check the Including row height and Including column width options before clicking OK. This is useful when structural appearance matters, such as for creating templates or clean reports.

3. In the destination selection box that appears, specify the top-left cell of your target paste range, then click OK.
a screenshot of selecting a destination cell

All chosen ranges will be pasted consecutively into the destination location, preserving original dimensions if desired. This method is fast, avoids manual copy-paste repetitions, and offers flexibility to tailor the pasted result according to your workflow.

Click here for more information about Copy Multiple Ranges utility       

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now


Copy multiple ranges from many worksheets/workbooks

Aggregating pieces of data from numerous worksheets or across different workbooks is commonly needed for summary reports or data analysis. Doing this manually is time-consuming and can be prone to mistakes, especially when dealing with large files. Kutools for Excel provides a Combine Worksheets utility that makes copying specified ranges from multiple sheets or even workbooks a simple, guided process. This tool is perfect for monthly consolidations, merging survey results, invoicing data, and other tasks where data is distributed across several sources.

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now

1. Start by clicking Kutools Plus > Combine on the Excel ribbon.

2. In the Combine Worksheets – Step 1 of 3 dialog box, choose Combine multiple worksheets from workbook into one worksheet and click Next.
a screenshot of choosing the Combine multiple worksheets from workbook into one worksheet option in the Combine Worksheets wizard

3. In Step 2 of 3, do the following:
a screenshot of selecting a workbook and the ranges in different worksheets you will copy
  (1) In the Workbook list, select the workbook and then click the a screenshot of the selection button Browse button following each sheet to pick the exact range you want.
  (2) To include additional workbooks, click Add, then repeat (1) to specify ranges from the extra files.
  (3) Click Finish after confirming all selections.

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now

4. A prompt will appear asking if you want to save your settings as a scenario for future use. Click Yes to save or No to proceed without saving.

Upon completion, all specified ranges from various sheets or workbooks will be automatically collected and placed in a new worksheet or workbook without the need for repeated manual copy-pasting. This preserves your data’s structure and drastically accelerates compiling distributed information.


Copy and consolidate multiple ranges using Excel formulas

For users who want to aggregate the content of non-contiguous ranges into a single list or summary without using VBA or the Clipboard, Excel formulas can be very helpful. This is especially suitable when working with structured datasets, such as gathering all data from several columns or lists into one summary area. Here is a practical example using the following formulas:

1. Suppose you want to combine data from ranges A1:A5 and C1:C5 into a single column starting from cell E1. Enter the following formula in E1:

=VSTACK(A1:A5, C1:C5)

2. Press Enter to confirm. All values from both ranges will appear stacked in E1 and downward. If you need to arrange all data from several non-adjacent ranges into a summary range elsewhere, simply modify the ranges inside VSTACK.

Notes:
  • The VSTACK function is only applied to Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web.
  • If your Excel version does not support VSTACK, you can use the following formula to copy ranges. Here's an example for merging A1:A5 and C1:C5 into E1:E10:
    =IF(ROW(A1)<=5,INDEX(A$1:A$5,ROW(A1)),INDEX(C$1:C$5,ROW(A1)-5))
    Enter this formula in cell E1 and press Enter, then drag it down to E10. This will place the first 5 values from A1:A5 and the next 5 from C1:C5 in a single column. Adjust the range sizes and references as needed for your source data.

Precautions and notes: These formulas are ideal for static data summaries but will not preserve formatting, formulas, charts, or merged cells from the source ranges. They are best used for consolidating lists or simple tables. If you encounter errors like #REF!, double-check range boundaries.

When working with multiple ranges, always double-check your selections, and consider creating a backup before performing bulk actions—especially if running VBA scripts or using add-ins in busy workbooks. If you encounter unexpected paste results, examine the source formatting and the structure of your destination area. Kutools for Excel is designed to simplify these processes, offering stable tools to handle them efficiently. Choose the method that best fits your scenario: for occasional use, the Clipboard or Excel formulas may suffice; for frequent or large-scale tasks, leveraging VBA or Kutools utilities delivers the most convenience and accuracy.


Demo: Copy multiple selected ranges from one worksheet

 

Demo: Copy multiple selected ranges from many workbooks/worksheets

 

Kutools for Excel: Over 300 handy tools at your fingertips! Enjoy permanently free AI features! Download Now!

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more
Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...


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!

All Kutools add-ins. One installer

Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.

Excel Word Outlook Tabs PowerPoint
  • All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
  • One installer, one license — set up in minutes (MSI-ready)
  • Works better together — streamlined productivity across Office apps
  • 30-day full-featured trial — no registration, no credit card
  • Best value — save vs buying individual add-in