Copying and pasting are useful because they allow transferring data from one place to another without retyping it each time. In Microsoft Excel, the maximum time we have a large dataset and we need that data in many sheets. Instead of typing the same thing again and again, we can copy multiple cells to another sheet in Excel. In this article, we will demonstrate different ways to copy multiple cells to another sheet in excel.
Download Practice Workbook
You can download the workbook and practice with them.
9 Different Methods to Copy Multiple Cells to Another Sheet in Excel
If we need to work on the same dataset which is already in one of the sheets of our spreadsheet, copying the cells can save time and make the work faster and more effective. To copy multiple cells to another sheet we are going to use the following dataset. The dataset contains some car details, the car models, the brand of those specific cars, the color of those cars, and the price of each car. Now, let’s go through the methods to copy multiple cells to another sheet in excel.
1. Copy Numerous Cells to Another Sheet Manually
Suppose, we need the data range B5:C8. To copy multiple cells to another sheet manually, we need to follow the procedure below.
STEPS:
- Firstly, select the data from the source sheet.
- Secondly, right-click on your mouse.
- After that, select Copy.
- After selecting Copy, we can see a green dotted box line which means the cells are copied now.
- Then, go to another sheet and again right-click on your mouse.
- And, select the Paste option.
- Finally, we can see that the selected multiple cells are now copied in another sheet.
Read More: How to Copy and Paste Multiple Cells in Excel (7 Quick Ways)
2. Copy Non-Adjacent Cells to Another Sheet
Assume that we need the data in column B and column E. The cells are non-adjacent, but in Excel, we can copy non-adjacent multiple cells to another sheet. Let’s follow the steps below.
STEPS:
- Likewise, in the previous method, select the cells from the source sheet that we want to copy. So, we copy data range B4:B11 and E4:E11.
- Next, right-click on the right mouse button.
- After that, choose Copy.
- A green dotted box line appears after selecting Copy, indicating that the cells have been copied.
- Then switch to another sheet and right-click your mouse once more.
- Select Paste from the drop-down menu.
- Finally, we can see that the selected cells have been copied to a new sheet.
Read More: Excel VBA: Copy Cell Value and Paste to Another Cell
3. Using Ribbon to Copy Multiple Cells in Excel
Instead of right-clicking on the mouse, we can use the ribbon on our excel file to copy multiple cells to another sheet. Presume that we need the data range B4:E7. Now to copy the range or multiple cells we need to follow the procedure down.
STEPS:
- In the first place, select the cells that we wish to copy in another sheet, so we select data range B4:E7.
- In the second place, go to the Home tab from the ribbon.
- After that, click the Copy drop-down menu and choose Copy on the Clipboard category.
- After clicking Copy, a green dotted box line appears, indicating that the cells have now been copied.
- Then, in another sheet, select a cell and again go to the Home tab.
- After that, select Paste.
- And in the end, the copied cells are now pasted in another sheet.
Read More: Excel VBA to Copy Only Values to Destination (Macro, UDF, and UserForm)
Similar Readings
- Excel Formula to Copy Cell Value from Another Sheet
- How to Copy Visible Cells Only in Excel (4 Fast Ways)
- Copy a Worksheet in Excel (5 Smart Ways)
- Exchange (Copy, Import, Export) Data Between Excel and Access
- How to Use Paste Special Command in Excel (5 Suitable Ways)
4. Keyboard Shortcuts to Copy Multiple Cells to Another Sheet
The shortcuts of the keyboards help us to do work faster and also those shortcuts save time. Suppose, we want to copy multiple cells range B4:E8 from our source sheet, to use the keyboard short to copy the cells we have to follow the strategy below.
STEPS:
- Firstly, select the cells that we want to copy. So we select the cell range B4:E8.
- Then, after that press the keyboard shortcut Ctrl + C to copy the cells.
- This will appear as a green dotted link around the range, which specifies the cells are copied now.
- After that, go to another sheet where the copied data will be pasted.
- And, to paste the data press Ctrl + V, keyboard shortcut.
- And, that’s it! We can see the result.
Read More: How to Copy and Paste in Excel and Keep Cell Size (7 Examples)
5. Copy Multiple Cells to Another Sheet with Paste Special
By using Paste Special, we can do a lot of work in excel. Let’s see the steps down to use the Paste Special feature to copy multiple cells to another sheet in Excel.
STEPS:
- In the beginning, select the cells that we want to copy, so we select cell range B4:E7.
- Just go to the Home tab again from the ribbon in the second place.
- Select Copy on the Clipboard group from the Copy drop-down menu.
- Now, go to another sheet where we want to paste the data.
- Then, from the ribbon, go to the Home tab.
- After that, in the Clipboard category, select Paste Special from the Paste drop-down menu bar.
- This will open the Paste Special dialog box.
- Next, on the Paste option box, click All. And, None from the Operation option box.
- Then, click on the OK button.
- And, finally, by doing this the cells are now copied in another sheet.
Read More: How to Apply VBA PasteSpecial and Keep Source Formatting in Excel
6. Clipboard Option to Copy Multiple Cells to Different Excel Sheet
We can use the Clipboard to copy multiple cells to another sheet in excel. We just need to follow some steps down.
STEPS:
- In the first place, select the data from the source sheet.
- Then, go to the Home tab on the ribbon.
- After that, click on the tiny icon on the Clipboard category.
- This will appear in the options list of the Clipboard.
- We can see the copied list and just clicking on that list will paste the selected cells.
- And, after clicking on that list, we can see that the copied cells are now in a new sheet.
Read More: How to Paste From Clipboard to Excel Using VBA
Similar Readings
- Copy Unique Values to Another Worksheet in Excel (5 Methods)
- How to Copy Merged and Filtered Cells in Excel (4 Methods)
- Copy Data from One Cell to Another in Excel Automatically
- VBA Code to Compare Two Excel Sheets and Copy Differences
- [Fixed]: Right Click Copy and Paste Not Working in Excel (11 Solutions)
7. Duplicate Several Formula Cells to Alternative Sheet in Excel
We can copy a formula to copy the cells. Let’s go through the steps below.
STEPS:
- In the first place, click on the cell that has the formula you want to copy. So we click on cell E5.
- And we can see the formula in the formula bar. From the formula, bar copy the formula by pressing Ctrl + C.
- Then, go to another sheet and paste the formula in cell E5 by pressing Ctrl + V.
- Drag the Fill Handle down over the range to copy the formula.
- And, this is it! The whole range of cells is copied in the new sheet now.
Read More: How to Update One Excel Worksheet from Another Sheet Automatically
8. Use Filter Feature to Duplicate Multiple Cells to Other Sheet
We can use the filter option to duplicate multiple cells to another sheet. Suppose we want to copy only the Hyundai list details. To use the filter option let’s go through the procedure below.
STEPS:
- By the same token, select the whole range B4:E11.
- Then, go to the Data tab from the ribbon.
- After that, click on the Filter on the Sort & Filter category.
- Next, click on the drop-down filter menu of the Brand.
- Select Hyundai, as we want to copy only the Hyundai details.
- Now, click on the OK button.
- This will sort and filter the data.
- Here, copy the cells by selecting them and pressing Ctrl + C.
- After copying, a green dotted box line appears, indicating that the cells have now been copied.
- Finally, go to another sheet and press Ctrl + V to paste the data.
- And, there you go! By using this we can see that multiple cells from another sheet are copied now.
Read More: How to Autofilter and Copy Visible Rows with Excel VBA
9. Excel VBA to Duplicate Multiple Cells
To copy multiple cells to another sheet in excel we can use Excel VBA. So, let’s see how we can use the VBA code to do this.
STEPS:
- Firstly, go to the Developer tab from the ribbon.
- Secondly, click on Visual Basic to open the Visual Basic Editor.
- Instead of doing this use the keyboard shortcut Alt + F11 to open the Visual Basic Editor.
- Or, right-click on the sheet and go to View Code.
- This will take you to the Visual Basic Editor, where you can write the code to copy multiple cells to another sheet.
- After that, write the VBA code below.
VBA Code:
Sub Copy_Multiple_Cells_to_Another_Sheet()
Sheets("VBA").Range("B4:C11").Copy (Sheets("VBA_Copied").Range("B4:C11"))
End Sub
- Next, run the code by clicking on the Run Sub button or pressing F5.
- You don’t need to change the code, you can only change the range as per your requirements.
- And, finally, this will copy the data from another sheet.
Read More: Macro to Copy Data from One Workbook to Another Based on Criteria
Multiple Cells Selection Copy Error in Excel
While copying multiple cells, sometimes we might face an Excel error message shown in the picture below. It’s because we can’t select multiple random cells. We have to select either from the same columns or the same rows. So while selecting make sure that your selection is in the same column/rows or not otherwise you will get that error message.
Read More: Copy and Paste is Not Working in Excel (9 Reasons & Solutions)
Conclusion
The above methods will assist you to copy multiple cells to another sheet in Excel. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Or you can have a glance at our other articles in the ExcelDemy.com blog!
Related Articles
- VBA Paste Special to Copy Values and Formats in Excel (9 Examples)
- Formula to Copy and Paste Values in Excel (5 Examples)
- Excel VBA to Copy Rows to Another Worksheet Based on Criteria
- Use VBA to Paste Values Only with No Formatting in Excel
- How to Copy and Paste Visible Cells Only in Excel (3 Easy Ways)
- Copy Multiple Rows in Excel Using Macro (4 Examples)
- How to Copy the Same Value in Multiple Cells in Excel (4 Methods)