WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » Copy Paste in VBA

Copy Paste in VBA

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

Copy Paste in VBA is similar to what we do in excel worksheet, like we can copy a value and paste it to another cell also we can use paste special to paste only the values, similarly in VBA we use the copy method with range property to copy a value from one cell to other and to paste the value we use the worksheet function paste special or paste method.

How to Copy Paste in VBA?

Below are some examples of how to copy-paste in excel using VBA.

The basic thing we do in excel is we copy, we cut, and we paste the data from one cell to another cell. It requires no special introduction as well. However, while learning VBA coding is important to understand the same concept in coding language. Copy Paste in VBA is the routine task we do in the day in day in excel. In order to copy first, we need to decide which cell to copy.

Copy Paste in VBA

Example #1 – Copy and Paste Values Using Range Object

You can download this VBA Copy Paste Excel Template here – VBA Copy Paste Excel Template

Assume you have the word “Excel VBA” in the cell A1.

VBA Copy Paste Example 1

Let’s say, for example, if you want to copy the cell A1 we can use the VBA RANGE object.

Code:

Sub Copy_Example()

  Range ("A1").

End Sub

VBA Copy Paste Example 1-1

The moment you reference the cell, we can see all the properties and methods with it. So select the method “Copy.”

Code:

Sub Copy_Example()

  Range("A1").Copy

End Sub

After selecting the method, press the space key to see the argument of the Copy method.

VBA Copy Paste Example 1-2

It says Destination.

This is nothing, but where do you want to copy-paste values in VBA without selecting the PASTE method.

If we are pasting in the same sheet, we can select the cell by using the Range object. Let us say if we want to paste the value in B3 cell, we can put the destination as “Range(“B3”).”

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Code:

Sub Copy_Example()

  Range("A1").Copy Destination:=Range("B3")

End Sub

This will copy the data from cell A1 and paste in cell B3.

VBA Copy Paste Example 1-3

We can also use the below method to paste the data.

Code:

Sub Copy_Example()
  
  Range("A1").Copy
  Range("B3").Select
  ActiveSheet.Paste

End Sub

First, we will copy & select the data from cell A1 and paste in cell B3.

Example 1-4

Example #2 – Copy to another Worksheet in the Same Workbook

Now, if we want to copy-paste the value from the different worksheets using VBA macro, then in the Destination argument, we need to reference the sheet name by using the WORKSHEETS object then mention the range of cells in that WORKSHEET. The below code will do the job.

Code:

Sub Copy_Example()

  Range("A1").Copy Destination:=Worksheets("Sheet2").Range("B3")

End Sub

If we want to copy the data from a particular sheet and want to paste in another particular sheet, we need to mention both the names of the sheets.

Example 2

Firstly we need to mention the copying sheet.

Worksheets("Sheet1").Range("A1").Copy

Then in the Destination argument, we need to mention the targeted worksheet name and range of the cell.

Destination:=Worksheets("Sheet2").Range("B3")

So the code should like this.

Code:

Sub Copy_Example()

  Worksheets("Sheet1").Range("A1").Copy Destination:=Worksheets("Sheet2").Range("B3")

End Sub

Example #3 – Copy from One Workbook to another Workbook

We have seen how to copy from worksheet to another worksheet in the same workbook. But we can also do this from one workbook to another workbook.

Take a look at the below code.

Code:

SubCopy_Example()

  Workbooks("Book 1.xlsx").Worksheets("Sheet1").Range("A1").Copy
  Workbooks("Book 2.xlsx").Activate

  ActiveWorkbook.Worksheets("Sheet 2").Select
  ActiveSheet.Paste

End Sub

Firstly it will copy the data from the worksheet “Sheet1” in the workbook “Book1.xlsx” from the cell A1.

“Workbooks("Book 1.xlsx").Worksheets("Sheet1").Range("A1").Copy”

Then it will activate the workbook “Book 2.xlsx”.

Workbooks("Book 2.xlsx").Activate

In the active workbook, it will select the worksheet “Sheet 2.”

ActiveWorkbook.Worksheets("Sheet 2").Select

Now in the active sheet, it will paste.

ActiveSheet.Paste

Alternative Way for using Copy-Paste in VBA

We have one more alternative way of having the data from one cell to another cell. Assume you have the word “Excel VBA” in the cell A1 and you need the same to come in cell B3.

Alternative Example 3

One method we have seen is using the VBA copy and paste method. Now I will show you one of the alternative ways. Look at the below piece of code to understand.

Code:

Sub Copy_Example1()

  Range("A1").Value = Range("B3").Value

End Sub

The above says whatever the value is there in the cell A1 should be equal to the value in the cell B3.

Range("A1").Value = Range("B3").Value

Even though this is not a copy and paste method still adds more value to our coding knowledge.

Top Ways of VBA Copy and Paste as Values

Now we will see different ways of VBA copy and paste values. Assume you are in the cell A1 as shown in the below image.

Example 4

  • If we want to copy and paste, we need to reference the cell here. Rather we can just use a property of Selection. Copy method.

Code:

Sub Copy_Example1()

  Selection.Copy Destination:=Range("B3")

End Sub

OR

Sub Copy_Example1()

  ActiveCell.Copy Destination:=Range("B3")

End Sub
  • If you want to copy the entire used range of the worksheet, you can use the below code.

Code:

Sub Copy_Example2()

  Worksheets("Sheet1").UsedRange.Copy Destination:=Worksheets("Sheet2").Range("A1")

End Sub

This will copy the entire used range in the worksheet “Sheet1” and will paste the same in the worksheet “Sheet2.”

Recommended Articles

This has been a guide to VBA Copy Paste. Here we discuss the top ways to Copy and Paste in VBA along with examples & a downloadable excel template. Below are some useful excel articles related to VBA –

  • Copy Formatting in Excel
  • Excel VBA Paste Values
  • Excel VBA File Copy Function
  • Excel VBA Paste
7 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Download Coursera IPO Financial Model

By continuing above step, you agree to our Terms of Use and Privacy Policy.
WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA Copy Paste Excel Template

Coursera IPO Financial Model & Valuation Free Download