3 Ways to Copy and Paste Cells with VBA Macros + Video

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

3 Ways to Copy and Paste in Excel with VBA Macros

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.xlsm (91.7 KB)

Video #1: The Simple Copy Paste Method

Videos best viewed in full screen HD.

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.xlsm (91.7 KB)

'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 _

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 = _
End Sub

Sub PasteSpecial_Examples()
'Use the Range.PasteSpecial method for other paste types

    'Copy and PasteSpecial a Range
    Range("A3").PasteSpecial Paste:=xlPasteFormats
    'Copy and PasteSpecial a between worksheets
    Worksheets("Sheet2").Range("A2").PasteSpecial Paste:=xlPasteFormulas
    'Copy and PasteSpecial between workbooks
    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.

Paste Data Below Last Used Row VBA Free Training

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!

Please share
Jon Acampora

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 140 comments
momna - January 8, 2018

i am new to vba and i want to make a dynamic drop down list with some enabling and disabling data.

waiting for your Response

snow - December 2, 2017

How about copying and pasting from one cell in one worksheet to another cell in another worksheet?

roma - November 21, 2017

Hi I do have some of your product. they are amazing,

I work with lots of data and copy and paste. do you have any magic which can be done more quick and easily

Robin - November 8, 2017

Hi Im Robin,

I Have a 100 more excel files with the different Employee names but the data is same i want to consolidate one excel sheet can you help me pls?

    Jeffrey - November 28, 2017

    I can be of help. Let’s keep in touch via email.

Mark - September 25, 2017

Hello Jon,

I tried to apply your videos on my current issues. I want to paste the value to sheet2 not in sheet 1 – hope you can help me– Thank you

Sub test()
Dim LastRow As Long
Dim cell As Range
Dim rng As Range

‘change sheet nam teo suit
With ThisWorkbook.Worksheets(“Sheet1”)

‘find lastrow in columns A:B
LastRow = Application.Max(.Cells(.Rows.Count, “A”).End(xlUp).Row, _
.Cells(.Rows.Count, “B”).End(xlUp).Row)
‘Copy values from A:B to D:E
.Range(“D2:E” & LastRow).Value = .Range(“A1:B” & LastRow).Value
‘Worksheets(“Sheet1”).Range(“D1:E” & LastRow).Value = Worksheets(“Sheet2”).Range(“A1:B” & LastRow).Value

‘Iterates throught each cell in D:E and if it equals to “” – remember it
For Each cell In .Range(“D2:E” & LastRow)
If cell.Value = “” Then
If rng Is Nothing Then
Set rng = cell
Set rng = Union(rng, cell)
End If
End If
‘delete all empty cells (with “”)
If Not rng Is Nothing Then rng.Delete Shift:=xlUp

End With
End Sub

Raihan - September 9, 2017

Dear Sir,
I want to copy one excel file to another excel file.
Not that, I want to copy whole sheet and then paste only value.
Unable to do .. Please help me..
Thanks in advance !

Philip - September 7, 2017

Thank you John for your great work and detailed presentation. I am wondering if there is a way to copy paste data beetween different workbooks.
Keep up the good work.

samir - August 28, 2017

D278 -31T212-5 +215.10-4

D278 -31T212-5 +215.10-4

it should be like

D278 -31T212-5

D278 +215.10-4

Satyajit Roychaudhury - August 26, 2017

Looking for a macro that would pick up the value of a cell in a column that I have selected and copy same to a specific cell in a different sheet to execute a series of calculation. A specific value from this calculation can then be displayed in the original sheet. I can then scroll to a different cell on the same column and repeat this process.

ian - August 10, 2017

I want to automatically copy information in cells A3:A21 and N3:N21 from sheet1 and paste in to A2:A37 in sheet2 and avoid blank cells.
Please can you help!

Ashima - July 27, 2017

Hi Jon!

Good morning

Can you help me with this:

I have a VERY LONG list where i have applied subtotal for the prices. And, as the subtotal appear just below the group, i need that headings to be appeared at the top also.

For example:


A car 12
B car 13
C car 14
car Total 39

D bike 34
R bike 56
bike Total 90

F scooter 24
H scooter 32
scooter Total 56

Grand Total 185

Harisul - July 10, 2017

I want to copy the file in range A1 to cel A8 as much data as in range A2; A10.
For example: if the data in range A2; A10 amounted to 4 then range A1 in copy to range A2 as much 4 too.

How to VBA code for it?

UMESH MAHADIK - July 8, 2017

I have a data like below

Name ID
A 1
A 2
C 3
C 4
C 5

I want to filter in name for eg. “A” then copy filtered data without header and paste in another sheet.
Then I want to fitler again in name “C” select filtered data and want to copy without header and paste to below of previously paste data.
Everytime my data will not be same.. Sometimes In name “A” Data will be more than 2 lines.

Kindly show how to do it by recording macro instead of writing macro.

Please help me
Thanks in advance.

danijel - July 3, 2017

Hi Jon and others,

i have approx. 20 sheets (call them “breakdown (BD) sheets”), each starts at cell A7, but overall range of BD sheets differs, because each of them is calculated from input data (e.g. BD sheet 1 has range from A7:F20; BD sheet – A7:F60 etc.). Additionaly, after finishing this mapping (from input data) on BD sheets, i need to convert formulas in these cells to values, except Total row, as each of BD sheets ends with Total row. So i would like to ask you, if is possible to include in one macro all these conditions, i.e. different range, and only up to Total row.


NICK - June 19, 2017

this does not work:

‘Range.Copy to other workbooks
Workbooks(“Book1.xlsx”).Worksheets(“Sheet1”).Range(“A1”).Copy _

run time error 9 subscript out of range. please let me know if there is a solution to this. thanks

Harisul - June 3, 2017

How to excel VBA code to copy and paste with the following criteria: I want to copy data on cells A1 and I want to copy as many data as in the right column. How is its excel VBA code?

Walter - May 25, 2017

Don’t know if this is possible at all but this is what I am trying to do.

I have a worksheet that pulls data from calculations from another worksheet. This worksheet is then copied and pasted data only and another sheet uses this data in further calculations. What I need is when the data is copied the first time with the macro for it to also copy a formula and put it into a different sheet depending on which option is selected in a drop down menu. Can you help?

Abhishek Mishra - May 12, 2017


Please help me as I want four different cell value to copy from different worksheet and then want to paste it in a new worksheet of same work book.

For eg:

I have a workbook name Book1.
It has 8 worksheet with different name.
Each worksheet has an Invoice in which I need four cell value like I8, D13, I9, J43. So I need these cell value from each worksheet and then want to simply paste the data in different worksheet of same workbook


Lorand - May 3, 2017


I have more than 1000 excel files.
I need to copy the first sheet from each excel file (without formula, just the values) and put in another excel file separately for each document.
Can you give me some ideas how can I do this automatically, please?


    Jon Acampora - June 1, 2017

    Hi Lorand,

    For this process you will need to loop through a list of the files, or all the files in a folder, open each file, copy/paste the values, then repeat for the next file. I don’t have any blog posts on this, but I do explain this process in detail in module 11 of my VBA Pro Course. In this module we build an application I call the File Manager that can be used to run any macro on a set of files. I have used this tool to automate budget processes and report consolidations.

    You can also use Power Query for this consolidation process. I hope that helps.

taza - April 5, 2017

i want to copy a table in workbook 1 to workbook 2 using the given example codes but when i run it, it always gives me error: Run-time error ‘9’ : Subscript out of range. How do i fix it?

raj kumar - April 3, 2017

Hi jon, Iam new to VBA topics. I always record macros in excel 2010. Right now. I want to copy the cell A1 TO A6 from one worksheet to another worksheet in any any column (say A1 to A6). Next time when i run the macros, it has to copy the same cell A1 to A6 from one worksheet to another worksheet in another column (like column wise values keeps on adding without replacing existing column). can u please help me to generate code.

Devendra - March 29, 2017

Hi I want to copy data from one worksheet to another worksheet in same workbook. Could you please help me.
I want to enter data in “Sheet1” range (A4- J4) and it will automatically paste in sheet 2 range (A2:J2) when I click button “Done”
And data of Sheet 1 which entered earlier will automatically remove so I can enter new data in same range (A4:J4) and when click done then it will paste on below old date (A3:j3).
I want to use same range to enter data and it will store to another sheet.

Please tell me code of this.
Thanks in advance

tony - March 24, 2017

Hi Jon,

I am attempting to copy a range of values from a worksheet called ‘import’, to a worksheet called ‘data’. The row of these two worksheets will change, so I can’t use the Range(“D2:E2”) function, I need to use the cells function. The macro works when I use the Range function, but is doesn’t work when I use the Cells function (see the sample code below). Can you help me to copy a range of cells between worksheets, that which enables me to use the Cells function please?

Worksheets(“data”).Range(Cells(2, 4), Cells(2, 5)).Value = Worksheets(“import”).Range(Cells(2, 4), Cells(2, 5)).Value



    Jon Acampora - March 29, 2017

    Hi Tony,
    The Cells properties with the Range property will also need the sheet reference. Otherwise, they will reference the active sheet.

    Worksheets(“data”).Range(Worksheets(“data”).Cells(2, 4), Worksheets(“data”).Cells(2, 5)).Value = Worksheets(“import”).Range(Worksheets(“import”).Cells(2, 4), Worksheets(“import”).Cells(2, 5)).Value

    I’m planning to add a video on this. It’s a very common issue. Thanks!

      Aditi - April 25, 2017

      This really helped me with my code.. I don’t have words to thank you.. Thanks a lot!!

      boa - June 29, 2017

      Thanks Jon! You helped me too…
      Really a common issue!

      Vijay - August 3, 2017

      Thank you Jon!. This code helped me a lot!

Asif - March 15, 2017


i have tried to use the following for copy paste. doesn’t work. Can you plz help.

Sub PasteSpecial_Examples()

‘Copy and PasteSpecial between workbooks
Workbooks(“LH_MONTHLY_20170131_No_MA_TEST.xlsm”).Worksheets(“CFR (Treasury)”).Range(“A1”).Copy
Workbooks(“LH_MONTHLY_20170131_V1_TEST.xlsm”).Worksheets(“CFR (Treasury)”).Range(“A1”).PasteSpecial Paste:=xlPasteFormats

‘Disable marching ants around copied range
Application.CutCopyMode = False

End Sub

salvatore - March 6, 2017

I refreshed page and all was well. Thanks

salvatore - March 6, 2017

Hi Jon,
I was watching the above videos (https://www.excelcampus.com/vba/copy-paste-cells-vba-macros/ ) and the first 2 videos are Identical. In fact, they’re both same duration as well as saying “1 of 3”. Is there a 2nd video to see? I would like to see it (perhaps it’s a bad link (going to the 1st video?)?)
Thank you

Shamna - March 1, 2017

Hi Jon,

Currently i am developing a macro tool for my work. i want to copy a no:of column values according to the user input numbers and paste it on an other sheet.

Can u help me for this.

Shamna H

Nandini - February 22, 2017

I am new to VBA. Kindly let me know how write a macro that will paste selected data from one cell into another cell in the same worksheet.

Kav. - February 20, 2017

Dear Jon,

Thanks for explaining this along with illustrative videos. On similar lines, can you please suggest VBA for copying text from a cell and pasting + searching that in google search tab.

For example, copying text string from Row 2 (D2, E2, F2, and so on) and pasting + searching the text strings in separate tabs in a browser window (www.google.com).

Thanks in advance.

Barry - February 9, 2017

Dear Jon;

I am an experienced programmer with over 30 years experience, including VB6, VB.NET, etc. I am in a new job where report data is generated in SQL, then put into hidden Worksheets. At that point Excel VBA code kicks in to copy the raw data into formatted worksheets. Platform is 64 bit Windows 10, 32 bit Office 365.

I am building a new report and am having the WORST time trying to copy and paste between worksheets! I get told “I can’t do that to a merged cell” when a cell is not merged, I get “Do you want to replace existing data?” when the cells are empty. Most infuriating, I define a SourceRange object of type Range and set it’s extent, but it doesn’t take! I see it on the spreadsheet, it looks right, but the .Rows.Count and Columns.Count values are wrong!

Is this stuff buggy beyond belief or am I missing something?

I’m not asking for a specific solution, just your general impressions. Is this platform messing with my head? I’m going nuts!



    Jon Acampora - February 15, 2017

    Hi Barry,
    Sorry to hear that. It sounds frustrating. I have not heard or experienced bugs like that. I typically unmerge all cells in a sheet first when doing copy/paste with VBA. Merged cells are difficult to work with in VBA and can cause problems. You might want to try unmerging all the cells on the sheets to see if that helps solve the issues. I can also refer you to a consultant to take a look at your project if you’d like. I hope that helps.

Vanessa - January 18, 2017


I tried using the same codes for Excel 2016 in Mac, sadly it doesn’t work. May I know is there a difference in coding VBA in Mac and Windows? I am new to VBA and urgently need help to create a macro for a project.

Many thanks.

    Jon Acampora - January 20, 2017

    Hi Vanessa,
    Unfortunately, Excel 2016 for Mac does not contain a solid VB Editor for development and debugging code yet. It’s very difficult, if not impossible, to write macros on the Mac 2016 version of Excel. Microsoft is working on updating it. You can cast your vote for this feature on the Excel User Voice site. Here is the link to the post about VBA for Mac where you can vote.

Umesh Jhamb - January 11, 2017

Hi Jon,

I am a beginner and started learning macros a few days ago.

I have created a Quality Form for my team. What I am trying to do is, Every time I audit a call, score someone and click on “Save”, all his scores should get save into Sheet 2. Next time when I audit another call, its data should save right below the previous call’s data on Sheet 2. So that I can calculate the average quality scores of all the agents and the team at one place.
I hope I was able to explain myself.

Could you please help!


SANDIP DAVE - December 30, 2016

I have a worksheet with live data of a currency value relative to a USD that updates every 10 minutes in cell e2, and a live clock in cell d2. I would like to record both of these values every 10 minutes in another worksheet, so I have an account of the daily volatility of the value. What is the best way to do this?

    Jon Acampora - January 4, 2017

    Hi Sandip,
    There are probably a few ways to do this. One way is to use the Application.Wait method in a loop. Set the wait for 10 minutes within a loop that repeatedly copies and pastes the data to a new sheet. The macro will have to be running all the time though. What is the process for updating the data every 10 minutes now? Is it a macro?

ricardo marrero - December 25, 2016

good afternoon ,please could you support to us in the following , I have a question , we have a range for example A1:D300 and we have some cells and the condition is “force” word , for example we could have it into some cells like D150 , D200 , D100 they have the word force , and if it is true , all the line and the contents of the cells A100 , B100, C100 and D100 ; A150 , B150 , C150 , D150 and A200, B200 , C200 and D200 those data should copied to another range

Thanks for your support


    Jon Acampora - December 27, 2016

    Hi Ricardo,

    I’m not sure I fully understand your question, but you might want to try filtering the range for the criteria, then copying the visible cells only. I hope that helps.

Kannan.G - December 6, 2016

I copied and pasted some data from sheet1 to sheet2 using VBA code.but the formula not calculating value with VBA pasted (Sheet2) Data please solve my problem.


ashok - November 25, 2016


copying from one workbook to another in the first video is not working for me.
it throws error
“Run time error 9 Subscript out of range”



Leave a Reply: