3 Ways To Copy And Paste Cells With VBA Macros In Excel

Bottom line: Learn 3 different ways to copy and paste cells or ranges in Excel with VBA Macros. This is a 3-part video series and you can also download the file that contains the code.

Skill level: Beginner

Copy & Paste: The Most Common Excel Action

Copy and paste is probably one of the most common actions you take in Excel. It's also one of the most common tasks we automate when writing macros.

There are a few different ways to accomplish this task, and the macro recorder doesn't always give you the most efficient VBA code.

In the following three videos I explain:

• The most efficient method for a simple copy and paste in VBA.
• The easiest way to paste values.
• How to use the PasteSpecial method for other paste types.

You can download the file I use in these videos below. The code is also available at the bottom of the page.

VBA Copy Paste Methods.zipDownload

Video #1: The Simple Copy Paste Method

You can watch the playlist that includes all 3 videos at the top of this page.

Video #2: An Easy Way to Paste Values

Video #3: The PasteSpecial Method Explained

VBA Code for the Copy & Paste Methods

Download the workbook that contains the code.

VBA Copy Paste Methods.zipDownload '3 Methods to Copy & Paste with VBA 'Source: https://www.excelcampus.com/vba/copy-paste-cells-vba-macros/ 'Author: Jon Acampora Sub Range_Copy_Examples() 'Use the Range.Copy method for a simple copy/paste 'The Range.Copy Method - Copy & Paste with 1 line Range("A1").Copy Range("C1") Range("A1:A3").Copy Range("D1:D3") Range("A1:A3").Copy Range("D1") 'Range.Copy to other worksheets Worksheets("Sheet1").Range("A1").Copy Worksheets("Sheet2").Range("A1") 'Range.Copy to other workbooks Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("A1").Copy _ Workbooks("Book2.xlsx").Worksheets("Sheet1").Range("A1") End Sub Sub Paste_Values_Examples() 'Set the cells' values equal to another to paste values 'Set a cell's value equal to another cell's value Range("C1").Value = Range("A1").Value Range("D1:D3").Value = Range("A1:A3").Value 'Set values between worksheets Worksheets("Sheet2").Range("A1").Value = Worksheets("Sheet1").Range("A1").Value 'Set values between workbooks Workbooks("Book2.xlsx").Worksheets("Sheet1").Range("A1").Value = _ Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("A1").Value End Sub Sub PasteSpecial_Examples() 'Use the Range.PasteSpecial method for other paste types 'Copy and PasteSpecial a Range Range("A1").Copy Range("A3").PasteSpecial Paste:=xlPasteFormats 'Copy and PasteSpecial a between worksheets Worksheets("Sheet1").Range("A2").Copy Worksheets("Sheet2").Range("A2").PasteSpecial Paste:=xlPasteFormulas 'Copy and PasteSpecial between workbooks Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("A1").Copy Workbooks("Book2.xlsx").Worksheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteFormats 'Disable marching ants around copied range Application.CutCopyMode = False End Sub

Paste Data Below the Last Used Row

One of the most common questions I get about copying and pasting with VBA is, how do I paste to the bottom of a range that is constantly changing? I first want to find the last row of data, then copy & paste below it.

To answer this question, I created a free training video on how to paste data below the last used row in a sheet with VBA. Can I send you the video? Please click the image below to get the video.

Free Training on Macros & VBA

The 3 videos above are from my VBA Pro Course. If you want to learn more about macros and VBA then checkout my free 3-part video training series.

I will also send you info on the VBA Pro Course, that will take you from beginner to expert. Click the link below to get instant access.

Free Training on Macros & VBA

Please leave a comment below with any questions. Thanks!