Copy Paste In VBA - WallStreetMojo

All Courses LoginAll BlogsVBA ResourcesCopy Paste in VBACopy Paste in VBA

Publication Date :

25 Apr, 2019

Blog Author :

Jeevan A Y

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Share

Master Complete Excel Learning PathDownload FREE Copy Paste in VBA Excel Template and Follow Along!Copy Paste in VBA Excel Template.xlsx

Table Of Contents

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 copy, cut, and paste the data from one cell to another. It requires no special introduction as well. However, learning VBA coding is important to understand the same concept in coding language. Copy paste in VBA is the routine task we do daily in Excel. To copy first, we need to decide which cell to copy.

🐍 Build Smarter Python Code with ChatGPT & AI Integration

Want to code faster and smarter? Build Python Programs with ChatGPT — teaches you how to use AI to write, debug, and optimize Python scripts with ease. Learn to automate coding tasks, integrate APIs, and build real-world applications—perfect for beginners, developers, and tech-savvy professionals looking to supercharge their productivity with AI.

Learn More →

Example #1 - Copy and Paste Values Using Range Object

Assume you have the word "Excel VBA" in cell A1.

For example, if you want to copy cell A1, we can use the VBA RANGE object.

Code:

Sub Copy_Example() Range ("A1"). End Sub

We can see all its properties and methods when you reference the cell. So, select the method "Copy."

Code:

Sub Copy_Example() Range("A1").Copy End Sub

After selecting the method, press the "Spacebar" key to see the argument of the copy method.

It shows "Destination."

It 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 using the Range object. For example, let us say if we want to paste the value in the B3 cell, we can put the destination as "Range("B3")."

Code:

Sub Copy_Example() Range("A1").Copy Destination:=Range("B3") End Sub

It will copy the data from cell A1 and paste it into cell B3.

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 and select the data from cell A1 and paste it into cell B3.

Build Python Programs with ChatGPT - Automate Coding & AI Integration Learn to build real-world Python projects faster using the power of ChatGPT. Automate code generation, debugging, and AI-driven problem solving while mastering core Python skills. Earn a recognized certification and gain hands-on experience that’s perfect for developers, analysts, and anyone exploring the intersection of coding and AI. Learn More →

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

Suppose we want to copy-paste the value from the different worksheets using VBA macro, then in the Destination argument. In that case, we need to reference the sheet name using the WORKSHEETS object and 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 specific sheet and paste it into another separate sheet, we need to mention both the names of the sheets.

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 be like this.

Code:

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

Excel All-In-One Bundle Learn to master formulas, automate tasks with VBA, build interactive dashboards, and analyze data like a pro—all in one comprehensive bundle. Develop hands-on Excel skills across all levels, earn a recognized certification, and gain practical knowledge that’s essential for roles in finance, analytics, and business operations. Learn More →

Example #3 - Copy from One Workbook to another Workbook

We have seen how to copy from one 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 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

The active workbook 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 cell A1 and you need the same to come in cell B3.

One method we have seen is using the VBA copy and paste method. Now, we 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 cell A1 should be equal to the value in 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 cell A1 as shown in the below image.

• If we want to copy and paste, we need to reference the cell here. Rather, we can use the property of the 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

It will copy the entire used range in the worksheet "Sheet1" and paste it into the worksheet "Sheet2."

FINANCIAL MODELING & VALUATION (CORE + AI) BUNDLE @60% + 20% OFF – FLASH SALE

Master Financial Modeling, DCF & Valuation with AI | Includes 7 Courses + McDonald’s & Netflix Case Studies | Learn to Build Models & Pitch Decks Faster using ChatGPT.

Join WallStreetMojo YouTube

INVESTMENT BANKING MASTERY PROGRAM @60% + 20% OFF – FLASH SALE

Experience the Exact Training Used by Top Investment Banks | Learn FM, DCF, LBO, M&A, Accounting & More | Unlock Wall Street-Level Skills this Sale.

Join WallStreetMojo Instagram

CHATGPT & AI FOR EXCEL COURSE @60% + 20% OFF – FLASH SALE

Boost Productivity 10X with AI-Powered Excel | Automate Reports, Eliminate Errors & Advance Your Analytics Skills | Transform Your Workflow this Sale.

Join WallStreetMojo LinkedIn

EXCEL ALL-IN-ONE BUNDLE COURSE @60% + 20% OFF – FLASH SALE

Master Excel, VBA & Power BI Like a Pro | 70+ Hours of Expert-Led Training | Learn Real-World Applications & Earn Your Certification this Sale.

Join WallStreetMojo Facebook

Join WallStreetMojo X