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.


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.

Dataset of Excel VBA copy paste


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.

Modified dataset to copy and paste a single cell

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

Creating a new module window

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

Module window is opened

  • 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.

Copied and pasted the code in the module window then clicked on run

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.

Value copied from one cell to another using Excel VBA

Read More: Excel Formula to Copy Cell Value to Another Cell


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.

Modified dataset to copy and paste a range of cells

  • 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.

Pasted the code to copy a range of cells in the newly created module section and clicked on Run

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.

Range of cells copied to another location using Excel VBA

Read More: How to Copy and Paste Multiple Cells in Excel (7 Quick Ways)


How to Copy and Paste an Entire Column

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

Modified dataset to copy and paste an entire column

  • 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 code to copy an entire column applied in the module section then clicked on run

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.

Entire column copied to another location

Read More: Macro to Copy Specific Columns from One Worksheet to Another in Excel


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.

Dataset to copy and paste an entire row using VBA

  • 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 code to copy an entire row is applied then clicked on Run

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.

Entire row copied and pasted to another location

Read More: How to Copy Rows Automatically in Excel to Another Sheet (4 Methods)


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.

Modified dataset to copy and paste multiple ranges

  • 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.

Copied and pasted VBA code to copy multiple ranges in the module section then clicked on Run

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.

New dataset was created with the copied ranges using VBA macro


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.

Two different worksheets

  • 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 code to copy data from one sheet to another

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.

Values are copied from worksheet-1 and pasted in worksheet-2

Read More: How to Copy a Worksheet in Excel (5 Smart Ways)


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.

Two Excel workbooks in the local disk

  • 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.

Image showing the worksheets of the two workbooks


1. When Workbooks are Open and Not Saved

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

Opening Visual Basic from the developer tab of the parent workbook

  • 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 code applied to copy and paste from one workbook to another when files are not saved

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.

Values are copied to 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 code to copy and paste from one workbook to another when workbooks are open and saved

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.

Values copied to workbook 2


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 macro to copy from one workbook to another when reference workbook is closed

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.

Values are copied and asks your permission whether to save the file or not


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.

Dataset to copy and paste 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 code to copy cell format only

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.

Empty Table is created after running the macro

  • 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.

Data range is copied and pasted

Read More: VBA Paste Special to Copy Values and Formats in Excel (9 Examples)


How to Copy and Paste Links

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

  • 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 macro to copy and paste links

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.

Links are copied and pasted


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.

Dataset to copy and paste as picture

  • 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 code applied to copy and paste as picture

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.

Select destination cell

  • And the picture is copied and pasted as follows.

Picture of the dataset is copied and pasted


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.

Dataset to copy and paste formula

  • 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 code to copy and paste formula

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.

Exact formula is copied to another location


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.

Dataset to copy selected cell then paste

  • 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 macro to copy already selected data

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.

Select destination cell to paste copied values

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

Previously selected data is pasted in the new location


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.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Prantick Bala
Prantick Bala

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo