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
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 > 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.
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:
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.
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 Kutools Plus > 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:
(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.
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!