Excel VBA: Copy Range to Clipboard (7 Suitable Examples)

In Microsoft Excel, you can copy the range to the clipboard using the VBA code. In this article, we will show several examples of copying range to the clipboard using Excel VBA code. Here, we utilize different types of VBA code to do the job properly. I think you find this article very informative and gain lots of knowledge regarding Excel VBA.

How to Enable Clipboard Shortcut in Excel

In Microsoft Excel, most of us use copy and paste by pressing Ctrl +C and Ctrl +V respectively. But when you want to paste more than just the copied item. Then, you have to take help from the clipboard. To enable the clipboard shortcut, you need to follow the steps properly.

Steps

First, go to the Home tab on the ribbon.
Then, select the small tilted arrow from the Clipboard group.

As a result, a Clipboard dialog box will appear.
Then, select Option appeared at the bottom.

After that, enable Show Office Clipboard When Ctrl+C Pressed Twice option.

As a consequence, the Clipboard will open by pressing Ctrl+C Twice as a keyboard shortcut.

Copy Range to Clipboard with Excel VBA: 7 Suitable Examples

To copy range to the clipboard using Excel VBA, we have shown seven different examples through which you can do the job. In this article, we will show all the possible VBA codes and their explanation through which we have a clear understanding of these examples. Here, we will show several examples including copy specific range, copy range from a specific sheet or active sheet, copy multiple ranges, copy range as a picture, and use of the VBA button.

1. Copy Specific Range to Clipboard

Our first example is based on how to copy a specific range to the clipboard. In this example, we will copy the range of cells to the clipboard and then, paste it to a specific cell in the active sheet. We will show the VBA code and its explanation. Before doing anything, enable the Developer tab on the ribbon. Then, follow the steps.

Steps

First, go to the Developer tab on the ribbon.
Then, select the Visual Basic option from the Code group.

It will open up the Visual Basic window.
Then, go to the Insert tab at the top.
After that, select the Module option.

As a result, a Module code window will appear.
Write down the following code.

Sub Copy_Range_To_Clipboard1()
Range("B4:E11").Copy
Range("G4").Select
ActiveSheet.Paste
End Sub

Then, close the Visual Basic window.
Then, go to the Developer tab on the ribbon.
Select the Macros option from the Code group.

Then, the Macro dialog box will appear.
Select Copy_Range_To_Clipboard1 from the Macro name section.
After that, click on Run.

As a result, it will copy the range of cells in your dataset. See the screenshot.

Now, to confirm that the range is copied to the Clipboard, you need to go to the Home tab on the ribbon.
Then, select a small tilted arrow from the Clipboard group.

Now, you can see the copied range in the Clipboard.

🔎 VBA Code Explanation

Sub Copy_Range_To_Clipboard1()

First of all, provide a name for the sub-procedure of the macro

Range("B4:E11").Copy

Then, define the range of cells to copy.

Range("G4").Select
ActiveSheet.Paste

After that, select the specific cell and paste the copied range of cells.

End Sub

Finally, end the sub-procedure of the macro.

2. Copy Range from Active Sheet to Clipboard

Our second example is based on how to copy range from active sheet to clipboard. In this example, we will copy the range of cells from the active sheet. It will appear on the Clipboard. We will also show the VBA code and its explanation to have a better understanding. Follow the steps.

Steps

First, go to the Developer tab on the ribbon.
Then, select the Visual Basic option from the Code group.

It will open up the Visual Basic window.
Then, go to the Insert tab at the top.
After that, select the Module option.

As a result, a Module code window will appear.
Write down the following code.

Sub Copy_Range_To_Clipboard2()
ActiveSheet.Range("B4:E11").Copy
End Sub

Then, close the Visual Basic window.
Then, go to the Developer tab on the ribbon.
Select the Macros option from the Code group.

Then, the Macro dialog box will appear.
Select Copy_Range_To_Clipboard2 from the Macro name section.
After that, click on Run.

As a result, it will copy the range of cells in your dataset. See the screenshot.

Now, to confirm that the range is copied to the Clipboard, you need to go to the Home tab on the ribbon.
Then, select a small tilted arrow from the Clipboard group.

Now, you can see the copied range in the Clipboard.

🔎 VBA Code Explanation

Sub Copy_Range_To_Clipboard2()

First of all, provide a name for the sub-procedure of the macro

ActiveSheet.Range("B4:E11").Copy

Then, define the active sheet range of cells to copy.

End Sub

Finally, end the sub-procedure of the macro

Read More: Clear the Clipboard in Excel

3. Copy Range from a Specific Sheet to Clipboard

Our next example is based on how to copy range from a specific sheet to the Clipboard. In this example, we will copy the range of cells from the specific worksheet. It will appear on the Clipboard. We will also show the VBA code and its explanation to have a better understanding. Follow the steps.

Steps

First, go to the Developer tab on the ribbon.
Then, select the Visual Basic option from the Code group.

It will open up the Visual Basic window.
Then, go to the Insert tab at the top.
After that, select the Module option.

As a result, a Module code window will appear.
Write down the following code.

Sub Copy_Range_To_Clipboard3()
Worksheets("Specific Sheet").Range("B4:E11").Copy
End Sub

Then, close the Visual Basic window.
Then, go to the Developer tab on the ribbon.
Select the Macros option from the Code group.

Then, the Macro dialog box will appear.
Select Copy_Range_To_Clipboard3 from the Macro name section.
After that, click on Run.

As a result, it will copy the range of cells in your dataset. See the screenshot.

Now, to confirm that the range is copied to the Clipboard, you need to go to the Home tab on the ribbon.
Then, select a small tilted arrow from the Clipboard group.

Now, you can see the copied range in the Clipboard.

🔎 VBA Code Explanation

Sub Copy_Range_To_Clipboard3()

First of all, provide a name for the sub-procedure of the macro

Worksheets("Specific Sheet").Range("B4:E11").Copy

Then, define the worksheet name and the range of cells to copy.

End Sub

Finally, end the sub-procedure of the macro.

4. Copy Multiple Ranges to Clipboard

Our next example is based on how to copy multiple ranges to the Clipboard. In this example, we will copy multiple ranges. It will appear on the Clipboard. We will also show the VBA code and its explanation to have a better understanding. Follow the steps.

Steps

First, go to the Developer tab on the ribbon.
Then, select the Visual Basic option from the Code group.

It will open up the Visual Basic window.
Then, go to the Insert tab at the top.
After that, select the Module option.

As a result, a Module code window will appear.
Write down the following code.

Sub Copy_Range_To_Clipboard4()
Range("B4:C7,E4:E7").Copy
Set newbook = Workbooks.Add
Range("A1").PasteSpecial
End Sub

Then, close the Visual Basic window.
Then, go to the Developer tab on the ribbon.
Select the Macros option from the Code group.

Then, the Macro dialog box will appear.
Select Copy_Range_To_Clipboard4 from the Macro name section.
After that, click on Run.

As a result, it will copy multiple ranges in your dataset. See the screenshot.

Now, to confirm that the multiple ranges are copied to the Clipboard, you need to go to the Home tab on the ribbon.
Then, select a small tilted arrow from the Clipboard group.

Now, you can see the copied range in the Clipboard.

🔎 VBA Code Explanation

Sub Copy_Range_To_Clipboard4()

First of all, provide a name for the sub-procedure of the macro

Range("B4:C7,E4:E7").Copy

Then, define the multiple ranges to copy.

Set newbook = Workbooks.Add
Range("A1").PasteSpecial

After that, set a new book to add a new workbook. Then, define the range to apply the paste special.

End Sub

Finally, end the sub-procedure of the macro.

5. Enable Auto Copy Range with Single Click

Our Next example is based on enabling the auto copy range with a single click. In this example, we will show a code utilizing the VBA intersect and VBA If Statement through which you can select the range from your dataset and it will copy to the clipboard. Follow the steps.

Steps

First, go to the Developer tab on the ribbon.
Then, select the Visual Basic option from the Code group.

It will open up the Visual Basic window.
Now, write down the following code on the sheet’s code window.
To open the sheet’s code window, you need to double-click on the sheet in the Visual Basic window.

Private Sub Worksheet_SelectionChange(ByVal cTarget As Range)
If Not Intersect(cTarget, Range("B4:E11")) Is Nothing Then
cTarget.Copy
End If
End Sub

Then, close the Visual Basic window.
Go to the specific worksheet and Excel executes the macro automatically.
Now, select the range of cells by dragging the mouse.

Now, to confirm that the range is copied to the Clipboard, you need to go to the Home tab on the ribbon.
Then, select a small tilted arrow from the Clipboard group.

Now, you can see the copied range in the Clipboard.

🔎 VBA Code Explanation

Private Sub Worksheet_SelectionChange(ByVal cTarget As Range)

First of all, provide a private sub name of the macro

If Not Intersect(cTarget, Range("B4:E11")) Is Nothing Then
cTarget.Copy
End If

Then, use the VBA Intersect to return a range upon the assigned Arg’s intersection. Here, the VBA If condition implements and performs Copy after its affirmation.

End Sub

Finally, end the sub-procedure of the macro.

6. Utilizing VBA Button

Our next example is based on utilizing the VBA button to copy range to the clipboard. In this example. We will create a button and in that button code, we will apply a code to copy the range to the clipboard. Finally, when we click the button, it will copy the range to the clipboard. To understand the example properly, follow the steps.

Steps

To create a button, first, go to the Developer tab on the ribbon.
Then, go to the Insert drop-down option.

After that, select the Button option from the Insert drop-down option.

When you click on this button, the mouse cursor will turn into a plus (+) icon.
Then, draw the button shape.
After that, the Assign Macro dialog box will appear.
Then, write down the preferred Macro name.
Now, click on New.

It will open up a code window.
Write down the following code in there.

Sub Button1_Click()
ActiveSheet.Range("B4:E11").Copy
End Sub

Then, close the window.
Right-click on the button and a Content Menu will appear.
From there, select Edit Text to edit the button name.

Give your preferred name. We set the Copy range to Clipboard as the name.

Then, if you click on the button, it will copy the range of cells to the clipboard.

Now, to confirm that the range is copied to the Clipboard, you need to go to the Home tab on the ribbon.
Then, select a small tilted arrow from the Clipboard group.

Now, you can see the copied range in the Clipboard.

7. Copy a Range as Picture to Clipboard

Our final example is based on how to copy a range as a picture to the clipboard. In this example, we will use a VBA code that will copy the range as a picture to the clipboard. To understand the example, follow the steps.

Steps

First, go to the Developer tab on the ribbon.
Then, select the Visual Basic option from the Code group.

It will open up the Visual Basic window.
Then, go to the Insert tab at the top.
After that, select the Module option.

As a result, a Module code window will appear.
Write down the following code.

Sub Copy_Range_To_Clipboard5()
Worksheets("Copy as Picture").Range("B4:E11").CopyPicture Appearance:=xlScreen, Format:=xlPicture
End Sub

Then, close the Visual Basic window.
Then, go to the Developer tab on the ribbon.
Select the Macros option from the Code group.

Then, the Macro dialog box will appear.
Select Copy_Range_To_Clipboard5 from the Macro name section.
After that, click on Run.

As a result, the assigned range is copied as a picture.
If you press Ctrl+V, it will paste as a picture. See the screenshot.

Now, to confirm that the range is copied as a picture to the Clipboard, you need to go to the Home tab on the ribbon.
Then, select a small tilted arrow from the Clipboard group.

Now, you can see the copied range as a picture on the Clipboard.

🔎 VBA Code Explanation

Sub Copy_Range_To_Clipboard5()

First of all, provide a name for the sub-procedure of the macro.

Worksheets("Copy as Picture").Range("B4:E11").CopyPicture Appearance:=xlScreen, Format:=xlPicture

Then, define the specific worksheet from where the range of cells is to copy them as a picture. After that., define the format as well as appearance.

End Sub

Finally, end the sub-procedure of the macro.

Things to Remember

The used VBA codes are applicable when all the cells have values. If there is any formula in the dataset, then it will return an error. So, your dataset must be contained only values.

Download Practice Workbook

Download the practice workbook below.

Copy Range to Clipboard.xlsm

Conclusion

We have shown seven different examples of copying range to the clipboard using Excel VBA. All of the codes are very easy to use and user-friendly. In this article, we focused on all cases to copy range to clipboard. If you have any questions, feel free to ask in the comment section.

Get FREE Advanced Excel Exercises with Solutions!

Excel VBA: Copy Range to Clipboard (7 Suitable Examples)

How to Enable Clipboard Shortcut in Excel

Copy Range to Clipboard with Excel VBA: 7 Suitable Examples

1. Copy Specific Range to Clipboard

2. Copy Range from Active Sheet to Clipboard

3. Copy Range from a Specific Sheet to Clipboard

4. Copy Multiple Ranges to Clipboard

5. Enable Auto Copy Range with Single Click

6. Utilizing VBA Button

7. Copy a Range as Picture to Clipboard

Things to Remember

Conclusion

Related Articles

Leave a reply Cancel reply