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.
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.
'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.
Please leave a comment below with any questions. Thanks!
Hi, I have two workbooks opened (both can be different names) and I want to copy a Range from Workbook1 SheetX (unkown/active) to Workbook2 (where I know the Sheet name)
I even want the command to open the worksheet from a directory too. Do you have any suggestions on how to do this? Hope to hear from you. Many thanks.
Windows(“incident.xlsx”).Activate
Range(“A:U”).Select ‘this trows the error: application-defined or object-deferred error
Hi,
I need your support, I’m creating a workbook for daily report, and I’m stuck in the summary worksheet, in the VBA I don’t know how to link the information of the new report to be copied in the summary.
Sub GENERAR_REPORTE()
Sheets(“REPORTE DIARIO”).Copy After:=Sheets(ActiveWorkbook.Sheets.Count)
ActiveSheet.Shapes.Range(Array(“Button 1”)).Delete
ActiveSheet.Name = Range(“P7”).Value
Application.DisplayAlerts = False
Sheets(“REPORTE DIARIO”).Delete
Application.DisplayAlerts = True
End Sub
Sub REGISTRAR_REPORTE()
Worksheets(“SUMMARY”).Range(“A6”).Value = ActiveSheet.Name = Range(“P7”).Value.Range(“P7”).Value
Worksheets(“SUMMARY”).Range(“G6”).Value = ActiveSheet.Name = Range(“P7”).Value.Range(“B15”).Value
Worksheets(“SUMMARY”).Range(“H6”).Value = ActiveSheet.Name = Range(“P7”).Value.Range(“D15”).Value
Worksheets(“SUMMARY”).Range(“I6”).Value = ActiveSheet.Name = Range(“P7”).Value.Range(“F15”).Value
Worksheets(“SUMMARY”).Range(“J6”).Value = Worksheets(“REPORTE DIARIO”).Range(“H15”).Value
Worksheets(“SUMMARY”).Range(“K6”).Value = Worksheets(“REPORTE DIARIO”).Range(“J15”).Value
Worksheets(“SUMMARY”).Range(“L6”).Value = Worksheets(“REPORTE DIARIO”).Range(“L15”).Value
Worksheets(“SUMMARY”).Range(“M6”).Value = Worksheets(“REPORTE DIARIO”).Range(“N15”).Value
Worksheets(“SUMMARY”).Range(“N6”).Value = Worksheets(“REPORTE DIARIO”).Range(“P15”).Value
nice, helpfull
OK I’m having a little trouble making a macro for a project at work. What I need help with: i have one work book and user form on sheet one to fill in information and this user form i have a button that should copy range of cells on sheet2 (S6:S9) to the (L6:L9)(all of this is on sheet2 and the samr thing should happen sheet3 to sheet9. If i could get some help i would be grateful
Hi Jon,
I have a crunched data in one workbook and wanted paste it in other by using macro. Also the number of line items changes every day (increase or decrease), So help me to copy and pasting the data in main template without number mismatch.
Hello, nice writeup. One quick question- Instead of copying an orderly range of cells from one column, how may we copy multiple single cells & paste them into an orderly horizontal range?
For example copying B2, F9, and Q11 yet pasting them into A1:C1
Thank you!
I am trying to write VBA for the below.
If the value in column AA is =”-” and the value in column AC is $0.00 then copy the value in column X to Column AA. My data set is ever changing so I can not set specific ranges and it may be the case where I don’t have data the satisfies the above criteria.
I have a workbook with a few tabs in it. I want to copy a cell with a formula in it from one worksheet and paste it into a cell in a different worksheet. When I use “PasteSpecial Paste:=xlPasteFormulas” it turns the formula cell references into REF# errors. For example:
LEN(A2)-LEN(SUBSTITUTE(A2,” “,””))=0
becomes
LEN(REF#)-LEN(SUBSTITUTE(REF#,” “,””))=0
It’s pasting the formula into the destination worksheet, but it must think the cell reference is still related to the source worksheet…or something like that. Do you know what could be causing this? Thank you!
Can you tell me how to Cells from Sheets present in 4 or more Workbooks to one Final Workbook?
It would be really helpful.
Thank You.
i have 300+ excel workbooks.
my requirement is copy specific data from all workbooks to another new workbook though VBA, can this possible,
Yes it is possible
Sub Copy()
Dim Sheet1 as worksheet
Dim sheet2 as worksheet
Dim book1 as workbook
Dim book2 as workbook
Workbooks(“Book1.xlsx”).Worksheets(“Sheet1”).Range(“A1”).Copy
Workbooks(“Book2.xlsx”).Worksheets(“Sheet1”).Range(“A1”).PasteSpecial Paste:=xlPasteFormats
End sub
[email protected]
kindly share it
will this work to copy a formula and use it on next line?
Hi John,
With a macro I am copying the last row of a table and pasting below as PasteFormulas, but when I do this, it consider the row as a ” total row” of the table. I do it in two different tables and in one it paste it okay but in the other one it considers it as a “Total Row” of the table.
Can you help me?
Many thanks!
Dim lastRow1 As Long, erow1 As Long
lastRow1 = Worksheets(“WO_SendM”).Cells(Rows.Count, 1).End(xlUp).Row
For i = 3 To lastRow1
If Worksheets(“WO_SendM”).Cells(i, 9).Value = Me.Label164.Caption Then
Worksheets(“WO_SendM”).Cells(i, 4).Copy
Worksheets(“WO_SendM”).Cells(i, 5).Copy
erow1 = Worksheets(“WO_Ledger”).Cells(Rows.Count, 18).End(xlUp).Row
Worksheets(“WO_SendM”).Paste Destination:=Worksheets(“WO_Ledger”).Cells(erow1 + 1, 18)
End If
Next i
Is it possible to get two cell value 4 and 5 in another cell?
I wanted to maintain a formatting of one of my records. In which I want to copy formats of certain cells to no. of times the user enters this will reduce the time of formatting ( which is a lot for me). Can you please make a video on that plz?
SUPER
Hi jon,
Thanks for your this tutorial, but i need a urgent help if possible answer to me.
copy and paste only filter cells from one excel to other excel using VBA
excel-vba
I’m stuck with a problem when I try to copy filtered cells from one excel file and paste it onto another file. I am using a macro call to get data where I try to fix this given code. It is not working when I try to copy visible cells which i filtered.
Private Sub CommandButton1_Click()
Dim lrCD As Long
Dim fNameAndPath As Variant
Dim WB As Workbook
Dim SourceWB As Workbook
Dim WS As Worksheet
Dim ASheet As Worksheet
fNameAndPath = Application.GetOpenFilename(FileFilter:=”Excel Files (*.xlsx), *.xlsx”, Title:=”Seleziona il file da aprire”)
‘Sets the variables:
Set WB = ActiveWorkbook
Set ASheet = ActiveSheet
Set SourceWB = Workbooks.Open(fNameAndPath) ‘Modify to match
‘Copies each sheet of the SourceWB to the end of original wb:
For Each WS In SourceWB.Worksheets
WS.Copy after:=WB.Sheets(WB.Sheets.Count)
Next WS
SourceWB.Close savechanges:=False
Set WS = Nothing
Set SourceWB = Nothing
WB.Activate
ASheet.Select
Set ASheet = Nothing
Set WB = Nothing
Application.EnableEvents = True
lastrow = Worksheets(4).Cells(Rows.Count, 1).SpecialCells(xlCellTypeVisible).End(xlUp).Row
For i = 3 To lastrow
Worksheets(4).Cells(i, 16).SpecialCells(xlCellTypeVisible).Copy
erow = Worksheets(“CFF”).Cells(Rows.Count, 1).SpecialCells(xlCellTypeVisible).End(xlUp).Row
Worksheets(4).PasteSpecial xlPasteValues = Worksheets(“CFF”).Cells(erow + 1, 2)
Worksheets(4).Cells(i, 16).SpecialCells(xlCellTypeVisible).Copy
Worksheets(4).PasteSpecial xlPasteValues = Worksheets(“CFF”).Cells(erow + 1, 3)
Worksheets(4).Cells(i, 15).SpecialCells(xlCellTypeVisible).Copy
Worksheets(4).PasteSpecial xlPasteValues = Worksheets(“CFF”).Cells(erow + 1, 4)
Worksheets(4).Cells(i, 12).SpecialCells(xlCellTypeVisible).Copy
Worksheets(4).PasteSpecial xlPasteValues = Worksheets(“CFF”).Cells(erow + 1, 5)
Worksheets(4).Cells(i, 13).SpecialCells(xlCellTypeVisible).Copy
Worksheets(4).PasteSpecial xlPasteValues = Worksheets(“CFF”).Cells(erow + 1, 6)
Worksheets(4).Cells(i, 18).SpecialCells(xlCellTypeVisible).Copy
Worksheets(4).PasteSpecial xlPasteValues = Worksheets(“CFF”).Cells(erow + 1, 1)
Next i
Application.CutCopyMode = False
Sheets(4).Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Sheets(2).Select
End Sub
Thanks in advance
Hey, when you copy a range, there is some copy buffer somewhere in excel, I assume as a manipulatable object in VBA. Is there a way to access this and what’s it called? I want to see if it’s a collection or array, or something that can be split into that- in other words, something that can be iterated over. I don’t want to just paste in one drop. I want to choose which cells I paste based on other criteria.
I have list of unique names of players in column A in sheet 1. And in sheet 2 I have data of cricket score of different matches for that particular player with same unique names. And in sheet 3 I have data of same players for football matches. Now I want to first find the name which is in the sheet 1 from sheet 2 and cut that data from sheet 2 and paste in same row of that particulate player. And find and cut the data from sheet 3 and paste it in sheet 1 on same row in next available cell. Do this task one by one for all players till cell is empty. In some cases I have multiple rows in sheet 2 and sheet 3 for the same name. In that case add a new row under that name and cut paste all the data. And every time I will update the sheet 2 and sheet 3 with the new data with new names which I will add in in sheet 1 too it will update all the data in sheet 1 by clicking a single button.
I have tried lots of thin but still it is not working as I needed. Anybody can help me for this task…..
Need to have varaible entries in Range?
Copy from sheet1 and paste in sheet2 and repeate … also copy from sheet2 to sheet1
I wish to copy the specific cells values from sheet1 to sheet2 and calculations will happen and copy again data from Sheet2 to sheet1
this process i wish to repeate for multiple line items in excel.
PLease help with VBA
[email protected]
Thanks a lot for your videos.
Kindly ask your support on below matter.
I have two cells on of them is free nothing inside cells but the second one with different formulas and I wish to do copy paste for both of them at the same time with vba code. Could you please tell me how I do it when I want to add or insert rows (down fill).
Your kind support and assistance would be highly appreciated.
Hello I am in a quandry. I dont want to relearn a language I would just like a client side page i can put a list of common responses. Click copy to add to clipboard and I will paste in closing remark. used to be simplke. looking for simpilist solution
if you could help with why this errors out, would be most appreciated on how to fix. The error happens starting below the line created using equal = signs. if i enter a hard range like Range(“A1:D1”) to test this, it will work, it is only when i use the Range(Cells(t, 1), Cells(t, 4)).value do i get the error:
Sub doUNTILsample()
Dim i As Long, t As Long
Sheets(“Pending”).Select
t = 1 ‘set value of t to 0
i = Cells(Rows.Count, 2).End(xlUp).Row ‘find the value of the last occupied cell in COL B
i = i + 1 ‘take result of last cell and add 1 to it
‘this gives us the first blank row below the last cell
If Cells(i, 3) = “” Then ‘if COL C cell is empty – there are no new referrals
MsgBox “There are no new conflict checks to Import”
Else
Sheets(“tempSHT”).Cells.ClearContents
Do Until Cells(i, 3).Value = “” ‘do this loop until you find the first empty cell in COL C
If Cells(i, 3).Value > “” Then ‘IF COL C cell is not empty
Cells(i, 2).Value = “x” ‘change the corresponding value in COLB to “x”
‘===================================================
‘Copy the values of the selected row, COL C:F IN PENDING WORKSHEET TO
‘tempSHT, starting with ROW 1 (t), COL A:D
‘ERROR: ‘Application or Object defined error’
Worksheets(“tempSHT”).Range(Cells(t, 1), Cells(t, 4)).Value = _
Worksheets(“PENDING”).Range(Cells(i, 3), Cells(i, 6)).Value
‘===================================================
t = t + 1 ‘ add 1 to VAR t, (if more results, copy next row down)
i = i + 1 ‘ add 1 to VAR i, (test next row)
End If
Loop ‘REPEAT UNTIL NO VALUE IN CELL IN COL C
End If
End Sub
Great!!! But I want my data to copy paste from one sheet to another, once after the macro pastes data in sheet 2 it should automatically update the status in new column as “Updated”.
Example macro has copy pasted data present in column A and B from sheet1 to sheet 2. Now after pasting the data in sheet2 I want my macro to fill the column C (Header – Status) as “Updated” automatically.
Please help!!!
I’m going to pay you the biggest compliment I can in terms of documentation and explanation:
I’m an old IBM mainframe programmer. IBM had the BEST documentation. Their language reference docs would use a minimal number of examples to explain every imaginable question. Including questions which arose while reading one of the examples. Very rarely would I need to ask another engineer.
Microsoft, on the other hand, produces frustratingly incomplete and totally confusing docs. I NEVER use their references; I use pages and videos from smart guys on the internet. Like you. I love their products; I hate their docs.
You, my friend, are of the IBM model…
Brad
Thank you very much Sir.
You are doing great work.
Private Sub CommandButton1_Click()
Dim WSCOPY As Worksheet
Dim WSDEST As Worksheet
Dim LCOPYLASTROW As Long
Dim LDESTLASTROW As Long
Set WSCOPY = Workbooks(“REQUISITION.XLSM”).Worksheets(“MASTER”)
Set WSDEST = Workbooks(“REQUISITION.XLSM”).Worksheets(“2021”)
LDESTLASTROW = WSDEST.Cells(WSDEST.Rows.Count, “C”).End(xlUp).Offset(1).Row
WSCOPY.Range(“A3”).Copy WSDEST.Range(“C” & LDESTLASTROW)
LDESTLASTROW = WSDEST.Cells(WSDEST.Rows.Count, “B”).End(xlUp).Offset(1).Row
WSCOPY.Range(“J3”).Copy WSDEST.Range(“B” & LDESTLASTROW)
LDESTLASTROW = WSDEST.Cells(WSDEST.Rows.Count, “D”).End(xlUp).Offset(1).Row
WSCOPY.Range(“J35”).Copy
WSCOPY.Range(“J35”).PasteSpecial Paste:=xlPasteValues
Worksheets(“MASTER”).Range(“J35”).Copy WSDEST.Range(“D” & LDESTLASTROW)
Application.CutCopyMode = False
Range(“A3:D6”).Select
Selection.ClearContents
Range(“G5:H5”).Select
Selection.ClearContents
Range(“J3:K3”).Select
Selection.ClearContents
Range(“A12:J34”).Select
Selection.ClearContents
Range(“J1”).Value = Range(“J1”).Value + 1
End Sub
This is my Macro that I have set-up. My issue is that when I run the macro it then deletes my formula on my “Master” sheet that I have in J35 even though I am only asking it to copy the value to my other sheet. All of my commands I want to happen do but I also need my Master sheet to retain the formula in that cell for the next user. Could you please help me figure out how to fix this?
Thank you!
I have a file that needs to do this but my dashboard includes drop down list, therefore the data is not always located in the same range, it needs to be dynamic. Is there a way to write a code that somehow joint vlookup to find where it has to be copied and pasted? English is not my first language sorry for the typos. I have extracted the logic in a new excel to make it simple and then reply on the main file but still without finding the way for it to work.
how i can create Radio Button to To choose which customer on the table then button to copy this customer info (name, phon, …..) from table in sheet1 and paste in specific cells in the sheet2 (invoice)
thank you
I’m trying to make a command button to copy the row i have highlighted and paste that data into the next empty 10 rows under it. Is this possible?
Thanks for you help
Please, how to add a number to a another sheet example sheet1 A1 is added to Sheet2 A1 and the Anewer will be at Sheet2 A1 (Sheet1 A1 =1, Sheet2 A1 = 1, sheet1 A1 + Sheet2 A1 = Sheet2 A1 2
Excellent the tutorial . but I have need One File Another File import Macro or vba code .
Beginner and am able to make my first macro. Right on spot!
No time wastage, very helpful. Thanks so much
Obrigado pela contribuição.
Excelente!
Quando penso que sei alguma coisa, concluio… não sei nada.
Hai sir
I have the text, so I need to find the some special character and copy and paste the next column , how can I do in vba
For example,
Lakshmanan is the text, I want to fine only laksh and copy that and paste the next column
I downloaded “VBA Copy Paste Methods.xlsm” and found it very helpful. I was impressed, too, by the documentation shown on sheet “Cover”. Please let me know how you are able to hide all columns to the right of column “P” and all rows below row 53.
Thank you.
How to record cell value change in sheet 1 to a column with fill down in sheet so the value change is recorded each time?
I have a spreadsheet to calculate and track my work hours.
In the top of the sheet, I enter my start time in E4 and my end time in G4. K4 contains the difference in HH:MM format. I then use the mod function to change the HH:MM from K4 to display the decimal equivalent in K5.
I’m trying to automate the lower half of the sheet that tracks my hours in decimals to be totalled weekly in Col K. I want a macro that I can run when I select the day of the week in the lower rows that will insert the value of the calculation stored in K5 above.
Any examples seem to be using a hard coded destination for the paste special value command. I want to select the day to apply that value to dynamically and then run the macro to paste into it.
I hope I have explained this enough for you to understand what I’m trying to do. If not, please let me know.
Thanks so much!
Dave L.
i am trying to create a simple macro that would copy from one sheet, and past to any selected cell on a different sheet. Can someone provide and example.
Thank You
Why aren’t you using RC notation when using VBA!?
Great tips! I always struggled with copying and pasting cells using VBA, but your step-by-step guide made it so much clearer. Can’t wait to try these methods in my next project!