How to copy multiple selections or ranges in Excel?
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
- Copy multiple selected ranges with VBA code
- Copy multiple selected ranges from one worksheet
- Copy multiple ranges from many worksheets/workbooks
- Copy and consolidate multiple ranges using Excel formulas
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 > 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:
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.

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.
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:
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.
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.
3. In Step 2 of 3, do the following:
(1) In the Workbook list, select the workbook and then click the 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.
- 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 mergingA1:A5
andC1:C5
intoE1: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 fromA1:A5
and the next 5 fromC1: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
Best Office Productivity Tools
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.





- 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