Create Command Button to Copy and Paste Data in Excel

Command buttons are an effective feature that can improve the functionality of your Excel spreadsheets and optimise your workflow. You may automate copying and pasting data by including a command button, which will streamline your work and lower the possibility of human error. This tutorial will walk you step-by-step through the process of building an Excel command button and giving it the ability to copy and paste data. Whether you're a novice or seasoned Excel user, this course will give you the information and abilities needed to use this practical tool.

You will have a command button by the end of this article that, when pressed, will copy data from one spot in your Excel spreadsheet and paste it to a different position of your choice. This will help you save time and effort, particularly when working with large or repeated information. So let's get started and explore Excel's command button features!

Create a Command Button to Copy and Paste Data

Here we will first insert a command button and then assign a macro to it to complete the task. So let us see a simple process to learn how you can create a Command Button to copy and paste data in Excel.

Step 1

Consider an Excel sheet where you have a command button.

Step 2

Then draw the box. Then right-click on the command button and select View Code, then copy the below code into the text box.

Right Click > View Code > Copy.

Code

Private Sub CommandButton1_Click()
   Application.ScreenUpdating = False
   Dim xSheet As Worksheet
   Set xSheet = ActiveSheet
      If xSheet.Name <> "Definitions" And xSheet.Name <> "fx" And xSheet.Name <> "Needs" Then
         xSheet.Range("A2:C6").Copy
         xSheet.Range("D2:F6").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
      End If
   Application.ScreenUpdating = True
End Sub

Step 3

Then close VBA using Alt + Q and exit the Design mode. From now on, when you click the command button, data from A2:C6 will be copied to D2:F6.

This is how you can create a command button to copy and paste data in Excel.

Conclusion

In this tutorial, we have used a simple example to demonstrate how you can create a Command Button to copy and paste data in Excel to highlight a particular set of data.