How to Copy and Paste Multiple Selections in Excel VBA

Copying and pasting multiple selections in Excel using VBA can be a powerful way to automate tasks within your spreadsheets. It enables you to efficiently duplicate data, formats, or formulas across different areas of your workbook. This tutorial will guide you through the process of using VBA to copy and paste multiple selections.


Before diving into the code, ensure you have your Excel workbook open with the source and target cells ready for the copy-and-paste operation.

1. Define the Range of Objects

Firstly, you need to define the range of cells that you want to copy from and the range where you want to paste the data. In VBA, this is done by creating Range objects:

2. Copy and Paste the Data

Once you’ve defined the source and target ranges, you can use the Copy method of the Range object to copy the data

After copying, you’ll paste the data into the target cell or range using the PasteSpecial method

The PasteSpecial method enables you to specify exactly what you want to paste. For instance, you could paste only the values, formulas, or formatting.

3. Multi-Range Selection

In scenarios where you have multiple discontinuous ranges to copy, you can combine them into one range using the Union method.vba

Similarly, you can use the PasteSpecial method to paste the combined ranges into a target range.

4. Clear Clipboard

After the copy and paste operation, it is often a good idea to clear the clipboard to prevent any unintended pasting later:

Full Code

Below is the full VBA code that demonstrates how to copy and paste multiple selections in Excel:

Ensure that you run this code in the Excel VBA editor. You can access the editor by pressing ALT + F11 in Excel.



With this tutorial, you have learned how to use Excel VBA to copy and paste multiple selections. You can define the range of cells to manipulate, use Copy and PasteSpecial methods to move data around and clear the clipboard when done.

Remember that VBA is a powerful tool, and with these techniques, you can significantly enhance your productivity by automating repetitive tasks in Excel.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.

Posted in vba