How to Copy and Paste in Excel Using VBA (12 Methods)

Get FREE Advanced Excel Exercises with Solutions!

Copy and Paste is one of the most frequent task done in Excel. And to work quickly and more efficiently, one should know the tips and tricks of using VBA to copy paste in Excel. We often need to copy data from one sheet to another, even sometimes from one workbook to another. You can copy your data manually for that but if you utilize VBA macro, you can make your work faster than ever.

In this article, we will learn how one can copy and paste data within the existing worksheet in Excel using VBA macro. Along with that, we will learn how to copy and paste from one worksheet to another using VBA. Also, we will crack the methods of copying and pasting data from one workbook to another in several circumstances using VBA.

We have used Excel 365 while preparing this article but the methods mentioned in this article are applicable in other Excel versions as well.

Download Practice Workbook

You can download the workbooks used to illustrate methods in this article from here.

Excel VBA Copy Paste.xlsm

Workbook 2.xlsx

How to Copy and Paste in Excel Using VBA: 12 Ways

We will be using the following dataset as an example to elaborate methods in this article. The dataset is representing some people’s brief descriptions.

How to Copy and Paste Value in a Single Cell

Let’s assume, you want to copy the text in cell D5 to cell F5 using VBA.

Press ALT+F11 to open the VBA window.
Now, click on Insert to expand and then select Module.

It will open a Module (Code) window as follows.

Insert the following code in this window.

Sub Paste_SingleCell()
Range("D5").Copy Range("F5")
End Sub

The code will copy the value in cell D5 and then will paste it in cell F5.

After that, click on the Run icon in the VBA window and then close the VBA window.

VBA Breakdown

Sub Paste_SingleCell()

Starts of the subroutine named Paste_SingleCell.

Range(“D5”).Copy Range(“F5”)

The Range function copies the value in cell D5 to cell F5.

End Sub

Ends the subroutine.

As a result, you will see that the value in cell D5 is copied to cell F5.

How to Copy and Paste Values in a Range of Cells

Suppose, you want to copy the values in range B5:C7 to the empty range F5:G7.

To copy and paste a range of cells using Excel VBA, first, create a new module just like the previous example.
Then insert the following VBA code in the module then click on Run.

Sub Copy_Range()
Range("B5:C7").Copy Range("F5:G7")
End Sub

The code will copy range B5:C7 and will paste it in range F5:G7.

VBA Breakdown

Sub Copy_Range()

Starts the subroutine titled Copy_Range.

Range(“B5:C7”).Copy Range(“F5:G7”)

Copies the values in range B5:C7 to range F5:G7.

End Sub

Ends the subroutine.

You can see that all the values in range B5:C7 are copied and pasted to range F5:G7.

How to Copy and Paste an Entire Column

Presume, you want to copy the entire column D then paste it to column F.

First, create a new module like previously.
Copy and paste the following VBA code in the new module window.
Press the Run button in the VBA window.

Sub Copy_column()
Range("D:D").Copy Range("F:F")
End Sub

This will copy column D and will paste the column in column F.

VBA Breakdown

Sub Copy_column()

This line initiates the subroutine titled Cppy_column.

Range(“D:D”).Copy Range(“F:F”)

Then the Range function copies all values along column D to column F.

End Sub

Ends the subroutine.

You can see that the entire column D has been copied into column F.

Similar Readings

How to Copy and Paste an Entire Row

Suppose, you want to copy the entire row 10 then paste it into row 12.

Create a new module.
Copy and paste the following code in the Module(Code).
After that, click on the Run icon.

Sub Copy_Row()
Range("10:10").Copy Range("12:12")
End Sub

This will copy row 10 and will paste the row in row 12.

VBA Breakdown

Sub Copy_Row()

Initiates the subroutine named Copy_Row.

Range(“10:10”).Copy Range(“12:12”)

The Range function copies all values along row 10 to row 12.

End Sub

Ends the subroutine.

After closing the VBA window, you will see that row 10 has been copied into row 12 as follows.

How to Copy and Paste Multiple Ranges

Assume, you want to copy the Employee ID column and the City column at the same time from the existing dataset then create a new dataset out of those copied columns. The Employee ID column is within range B4:B19 and the City column is within range E4:E19.

Create a new module as before.
Copy and paste the following VBA code then click on Run.

Sub Copy_Multiple_Ranges()
    Dim sourceRange As Range
    Dim destinationRange As Range
    Set sourceRange = Range("B4:B9,E4:E9")
    Set destinationRange = Range("B12")
    sourceRange.Copy destinationRange
    Application.CutCopyMode = False
    destinationRange.Select
End Sub

This will copy range B4:B9 and range E4:E9 then paste these to range B12:C17.

VBA Breakdown

Sub Copy_Multiple_Ranges()

Starts the subroutine named Copy_Multiple_Ranges.

Dim sourceRange As Range

Dim destinationRange As Range

Here, two variables sourceRange and destinationRange are declared as Range type.

Set sourceRange = Range(“B4:B9,E4:E9”)

The ranges from where the values will be copied are set here.

Set destinationRange = Range(“B12”)

The ranges where the values will be pasted are set here.

Copy destinationRange

This function copies the values in range B4:B9 and range E4:E9 to the destination range which’s first cell is cell B12.

CutCopyMode = False

After the function completes, the copied data won’t be saved in the clipboard since this line clears the copy mode.

Select

This function will select the destination cell B12 automatically after running the VBA macro.

End Sub

Ends the subroutine.

Finally, after running the VBA macro, you will get your output as follows.

How to Copy and Paste From One Worksheet to Another

You can use Excel VBA to copy data from one worksheet to another of the same workbook. In this example, we will copy data from a worksheet named Worksheet 1 to a worksheet named Worksheet 2.

Type the following code in the Module(Code).

Sub Copy_AnotherSheet()
Worksheets("Worksheet-1").Range("B4:D10").Copy _
Worksheets("Worksheet-2").Range("B4:D10")
End Sub

The code will copy the range B4:D10 from the Worksheet-1 sheet and paste it into range B4:D10 of the Worksheet-2 sheet.

Click on the Run icon and then close the VBA window.

VBA Breakdown

Sub Copy_AnotherSheet()

Initiates the start of the subroutine titled Copy_AnotherSheet.

Worksheets(“Worksheet-1”).Range(“B4:D10”).Copy Worksheets(“Worksheet-2”).Range(“B4:D10”)

.Copy copies all the contents in range B4:D10 of sheet named Worksheet-1 then paste those contents to range B4:D10 of sheet Worksheet-2.

End Sub

Ends the subroutine.

Now, If you open the Worksheet 2 sheet, you will see the range B4:D10 from the Worksheet 1 sheet is being copied and pasted into this sheet.

Similar Readings

How to Copy and Paste From One Workbook to Another

By utilizing VBA code, you can copy and paste values from one workbook to another just within a blink of an eye. You can copy and paste from one workbook to another in many circumstances.

Let’s assume, our existing workbook is Excel VBA Copy Paste and we want to copy values from this workbook and then paste those values in the Workbook 2 file.

The location of Workbook 2 is in the Local Disk E here.

We want to copy the dataset of sheet Workbook of Excel file Excel VBA Copy Paste then paste that dataset into Excel file Workbook 2 using VBA.
There can be many situations while copying and pasting from one workbook to another. Such as here, Workbook 2 can be opened but not saved or Workbook 2 can be opened and saved. Also, Workbook 2 can be closed as well.
Here, we will copy our data from the Excel VBA Copy Paste then paste those to Sheet1 of Workbook 2 when Workbook 2 is opened and not saved.
Then, we will copy our data from the Excel VBA Copy Paste to Sheet2 of Workbook 2 when Workbook 2 is opened and saved.
And, we will copy out data from sheet Workbook of file Excel VBA Copy Paste then paste those to Sheet 3 of closed file Workbook 2.
Initially, all the sheets in Workbook 2 are empty as follows.

1. When Workbooks are Open and Not Saved

Open Visual Basic window from the Developer tab from workbook Excel VBA Copy Paste.

Insert a new module window.
Copy and paste the following VBA code in the newly created module window.
Then click on Run.

Sub Copy_to_Another_Workbook_Open_Not_Saved()
Workbooks("Excel VBA Copy Paste").Worksheets("Workbook").Range("B4:D10").Copy _
Workbooks("Workbook 2").Worksheets("Sheet2").Range("B4:D10")
End Sub

VBA Breakdown

Sub Copy_to_Another_Workbook_Open_Not_Saved()

Starts the subroutine Copy_to_Another_Workbook_Open_Not_Saved.

Workbooks(“Excel VBA Copy Paste”).Worksheets(“Workbook”).Range(“B4:D10”).Copy Workbooks(“Workbook 2”).Worksheets(“Sheet2”).Range(“B4:D10”)

.Copy copies the range B4:D10 from the worksheet named Workbook within the workbook named Excel VBA Copy Paste and paste it into the same range on the worksheet named Sheet2 within another workbook named Workbook 2.

End Sub

Ends the subroutine.

You can see that the range B4:D10 from sheet Workbook of file Excel VBA Copy Paste is being copied to range B4:D10 of Sheet1 of file Workbook 2.

2. When Workbooks are Open and Saved

Create a new module in the file Excel VBA Copy Paste like previously.
Copy and paste the following VBA code in the module section and then click on Run.

Sub Copy_to_Another_Workbook_Open_Saved()
Workbooks("Excel VBA Copy Paste.xlsm").Worksheets("Workbook").Range("B4:D10").Copy _
Workbooks("Workbook 2.xlsx").Worksheets("Sheet1").Range("B4:D10")
End Sub

VBA Breakdown

Sub Copy_to_Another_Workbook_Open_Saved()

Starts the subroutine Copy_to_Another_Workbook_Open_Saved.

Workbooks(“Excel VBA Copy Paste.xlsm”).Worksheets(“Workbook”).Range(“B4:D10”).Copy Workbooks(“Workbook 2.xlsx”).Worksheets(“Sheet1”).Range(“B4:D10”)

.Copy copies the range B4:D10 from the worksheet named Workbook within the workbook named Excel VBA Copy Paste.xlsm and pastes it into the same range on the worksheet named Sheet1 within another workbook named Workbook 2.xlsx.

End Sub

Ends the subroutine.

Finally, you can see that the range B4:D10 from sheet Workbook of file Excel VBA Copy Paste is being copied to range B4:D10 of Sheet2 of file Workbook 2 as shown below.

3. When Reference Workbook is Closed

Create a new module in the file Excel VBA Copy Paste like previously.
Type down the following code in the module window then click on Run.

Sub Copy_to_Another_Workbook_Closed()
Workbooks.Open ("E:\Workbook 2.xlsx")
Workbooks("Excel VBA Copy Paste.xlsm").Worksheets("Workbook").Range("B4:D10").Copy _
Workbooks("Workbook 2.xlsx").Worksheets("Sheet3").Range("B4:D10")
Workbooks("Workbook 2.xlsx").Close
End Sub

VBA Breakdown

Sub Copy_to_Another_Workbook_Closed()

Initiates the subroutine named Copy_to_Another_Workbook_Closed.

Open (“E:\Workbook 2.xlsx”)

The Workbooks.Open opens the workbook named Workbook 2.xlsx located at the specified file path E:\Workbook 2.xlsx.

Workbooks(“Excel VBA Copy Paste.xlsm”).Worksheets(“Workbook”).Range(“B4:D10”).Copy Workbooks(“Workbook 2.xlsx”).Worksheets(“Sheet3”).Range(“B4:D10”)

.Copy copies the range B4:D10 from the worksheet named Workbook within the workbook named Excel VBA Copy Paste.xlsm and pastes it into the same range on the worksheet named Sheet1 within another workbook named Workbook 2.xlsx.

Workbooks(“Workbook 2.xlsx”).Close

Ask your permission whether you want to save the workbook Workbook 2.xlsx or not then closes the Workbook 2.xlsx.

End Sub

Ends the subroutine.

You will be asked whether you want to save the Workbook 2 file or not.
Also, you can see that the range B4:D10 from sheet Workbook of file Excel VBA Copy Paste is being copied to range B4:D10 of Sheet3 of file Workbook 2 as shown below.

How to Copy and Paste Using Paste Special in VBA

You can copy and paste particular cell characteristics with Paste Special (examples: formats, values, column widths, etc.). You can also use it to execute custom paste operations such as skip blanks and transpose.

Now, I’ll show you how you can use paste special in VBA by copying the format of our dataset. Here, we will copy the range B4:D10 to range F4:H10 using paste special in VBA.

Insert the following code in the VBA module window then click on Run.

Sub Paste_special()
Range("B4:D10").Copy
Range("F4:H10").PasteSpecial Paste:=xlPasteFormats
End Sub

The code will create a macro named Paste_special which will only copy the formats of cell range B4:D10 and will paste it in cell range F4:H10.

VBA Breakdown

Sub Paste_special()

Starts the subroutine Paste_special.

Range(“B4:D10”).Copy

.Copy copies the range B4:D10.

Range(“F4:H10”).PasteSpecial Paste:=xlPasteFormats

The copied values will then be pasted in range F4:H10 with the special format specified by the argument Paste:=xlPasteFormats. The PasteSpecial method is used to paste the copied content in a specific way, specified by the argument.

End Sub

Ends the subroutine.

After running the VBA code, you will see that the format of range B4:D10 is copied and pasted into range F4:H10.

Now click on the 1st cell of the pasted dataset and press Enter.
And you can see that the data range is copied and pasted as follows.

How to Copy and Paste Links

Assume, you want to copy the links in range C5:C7 and paste those in range E5:E7.

Create a new module like previously from the Visual Basic window.
Copy and paste the following VBA code in the module section.
Click on Run.

Sub Copy_Links()
    Dim sourceRange As Range
    Dim destinationRange As Range
    Dim cell As Range
    Set sourceRange = Range("C5:C7")
    Set destinationRange = Range("E1")
    For Each cell In sourceRange
        If cell.Hyperlinks.Count > 0 Then
            destinationRange.Cells(cell.Row, 1).Hyperlinks.Add _
                anchor:=destinationRange.Cells(cell.Row, 1), _
                Address:=cell.Hyperlinks(1).Address, _
                TextToDisplay:=cell.Hyperlinks(1).TextToDisplay
        End If
    Next cell
End Sub

VBA Breakdown

Sub Copy_Links()

Starts subroutine titled Copy_Links.

Dim sourceRange As Range

Dim destinationRange As Range
Dim cell As Range

Declare variables sourceRange, destinationRange, and cell as Range type.

Set sourceRange = Range(“C5:C7”)

Sets range of the variable sourceRange to range C5:C7.

Set destinationRange = Range(“E1”)

Sets the range of the variable destinationRange to cell E1.

For Each cell In sourceRange

If cell.Hyperlinks.Count > 0 Then
destinationRange.Cells(cell.Row, 1).Hyperlinks.Add _
anchor:=destinationRange.Cells(cell.Row, 1), _
Address:=cell.Hyperlinks(1).Address, _
TextToDisplay:=cell.Hyperlinks(1).TextToDisplay
End If
Next cell

The For Each loop is used to go through each cell in the sourceRange. The following operations are carried out by the code if a cell has one or more hyperlinks: It updates the destinationRange‘s relevant cell with a new URL. The cell in column 1 (column A) of the same row as the current cell in the loop is the cell that is referenced by the expression destinationRange.Cells(cell.Row, 1). The new hyperlink is identical to the first hyperlink discovered in the original cell in terms of URL and displayed text. Using the Next cell command, the code then advances to the following cell in the sourceRange. Until every cell in the sourceRange has been analyzed, this loop will keep running.

End Sub

Ends the subroutine.

And you can see that the links are copied to the new range as follows.

How to Copy and Paste as Picture

You can create a picture out of your dataset and then copy and paste it to your worksheet using VBA code.

Let’s say, you want to create an image out of the range B4:D10 from the following dataset then copy that image and paste it to your worksheet.

Create a new module.
Copy and paste the following code in the module then click on Run.

Sub Copy_As_Picture()
On Error Resume Next
    Dim sourceRange As Range
    Dim pictureRange As Range
    Dim pictureShape As Shape
    Set sourceRange = ActiveSheet.Range("B4:D10")
    Set pictureRange = Application.InputBox("Select a cell where you want to put the picture", Type:=8)
    pictureRange.Select
    sourceRange.CopyPicture Appearance:=xlScreen, Format:=xlPicture
    With pictureRange.Parent
        Set pictureShape = .Pictures.Paste(Link:=False)
    End With
    With pictureShape
        .Top = pictureRange.Top
        .Left = pictureRange.Left
        .Height = pictureRange.Height
        .Width = pictureRange.Width
    End With
End Sub

VBA Breakdown

Sub Copy_As_Picture()

Starts the subroutine Copy_As_Picture.

On Error Resume Next

Enables error handling and tells the programme to go on running in the event of an error.

Dim sourceRange As Range

Dim pictureRange As Range
Dim pictureShape As Shape

The sourceRange, pictureRange variables are set as Range type and pictureShape is of Shape type variable.

Set sourceRange = ActiveSheet.Range(“B4:D10”)

The sourceRange variable is representing the range B4:D10 on the active sheet.

Set pictureRange = Application.InputBox(“Select a cell where you want to put the picture”, Type:=8)

The pictureRange variable receives the address of the selected cell as a range object.
With the Type:=8 parameter, which designates a cell reference as the input type, the Application.InputBox function is applied.

Select

This function selects the cell set by the pictureRange variable.

CopyPicture Appearance:=xlScreen, Format:=xlPicture

This function copies an image of the sourceRange . Then, that has copied the range as an image by the CopyPicture operator. The image should be duplicated exactly as it appears on the screen, according to the Appearance:=xlScreen parameter. The format of the copied photo is specified by the Format:=xlPicture parameter.

With pictureRange.Parent

Set pictureShape = .Pictures.Paste(Link:=False)
End With

The With block defines the pictureShape object that starts here. pictureRange.Parent is a reference to the worksheet holding the picture, which is the pictureRange‘s parent object. The copied image is pasted onto the worksheet using this line.

The .Pictures.Paste(Link:=False) method is used to paste the picture without creating a link. With the With, it comes to an end here.

With pictureShape

.Top = pictureRange.Top
.Left = pictureRange.Left
.Height = pictureRange.Height
.Width = pictureRange.Width
End With

Here the properties of the pictureShape object (such as top position, left position, height, and width) are set to match the corresponding properties of the pictureRange cell.

End Sub

Ends the subroutine.

After running the VBA macro, choose a cell where you want to paste the created picture.
Here, we have selected cell B12 and then clicked on OK.

And the picture is copied and pasted as follows.

How to Copy and Paste Formula

You can copy formulas from your parent dataset and then paste the exact formulas to a new range using VBA macro.

Let’s assume, you have a dataset as follows where range F5:F9 contains formulas. You want to copy these values with the exact formulas used here to the range G5:G9.

Create a new module then copy and paste the following VBA code there.
Click on Run.

Sub Copy_Exact_Formula()
Range("G4:G9").Formula = Range("E4:E9").Formula
End Sub

VBA Breakdown

Sub Copy_Exact_Formula()

Initiates the subroutine Copy_Exact_Formula.

Range(“G4:G9”).Formula = Range(“E4:E9”).Formula

Copies the assigned formulas from the range E4:E9 to the range G4:G9.

End Sub

Ends the subroutine.

And, the range is copied with exact formulas as follows.

How to Copy Selected Cell then Paste

You can copy your already selected data and paste it wherever you want using VBA.

Like here, we have already selected range C8:D9. Now, you want to copy and paste this selection using VBA macro to range F5:G6.

Create a new module.
Enter the code below in the module section.
Click on Run.

Sub Selection_Copy_Paste()
Dim location As Range
Set location = Application.InputBox( _
      Title:="Number Format Rule From Cell", _
      Prompt:="Select a cell to pull in your number format rule", _
      Type:=8)
Selection.Copy location
End Sub

VBA Breakdown

Sub Selection_Copy_Paste()

Starts the subroutine Selection_Copy_Paste.

Dim location As Range

Declares a variable location as Range type.

Set location = Application.InputBox( Title:=”Number Format Rule From Cell”, Prompt:=”Select a cell to pull in your number format rule”, Type:=8)

This initiates an input box using the Application object’s InputBox function. The user can choose a cell to pull in a number format rule by clicking on it in the input box. The location variable receives the address of the selected cell as a range object. We have set the title of the input box using the Title option. When the user uses the Prompt parameter input field shows a prompt message. The Type parameter represents the cell reference 8, which determines the type of input box.

Copy location

This code copies the currently selected range (Selection) and pastes it into the specified destination range (location).

End Sub

Ends the subroutine

After running the code, choose the cell where you want to paste the copied values.
Select cell F5.
Click on OK.

And finally, the already selected range C8:D9 is copied and pasted to the range F5:G6 as follows.

Excel VBA Copy Paste: Knowledge Hub

Conclusion

I hope now you know how you can copy and paste in Excel VBA. If you have any type of confusion, please feel free to leave a comment. Also don’t forget to visit our site ExcelDemy to explore more relevant articles.

How to Copy and Paste in Excel Using VBA (12 Methods)

Download Practice Workbook

How to Copy and Paste in Excel Using VBA: 12 Ways

How to Copy and Paste Value in a Single Cell

How to Copy and Paste Values in a Range of Cells

How to Copy and Paste an Entire Column

How to Copy and Paste an Entire Row

How to Copy and Paste Multiple Ranges

How to Copy and Paste From One Worksheet to Another

How to Copy and Paste From One Workbook to Another

1. When Workbooks are Open and Not Saved

2. When Workbooks are Open and Saved

3. When Reference Workbook is Closed

How to Copy and Paste Using Paste Special in VBA

How to Copy and Paste Links

How to Copy and Paste as Picture

How to Copy and Paste Formula

How to Copy Selected Cell then Paste

Excel VBA Copy Paste: Knowledge Hub

Conclusion

Related Articles

What is ExcelDemy?

Leave a reply Cancel reply