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. Show
Skill level: Beginner Copy & Paste: The Most Common Excel ActionCopy 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:
You can download the file I use in these videos below. The code is also available at the bottom of the page. Video #1: The Simple Copy Paste MethodYou can watch the playlist that includes all 3 videos at the top of this page. Video #2: An Easy Way to Paste ValuesVideo #3: The PasteSpecial Method ExplainedVBA Code for the Copy & Paste MethodsDownload the workbook that contains the code. '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 RowOne 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 & VBAThe 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! Can you copy data from one worksheet to another using a VBA program?We can copy a value and paste it to another cell. 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 another. We use the worksheet function Paste Special or the paste method to paste the value.
|