3 Ways to Copy and Paste Cells with VBA Macros in Excel
127

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

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 127 comments
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?

Reply
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.

Reply
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.

thanks

Reply
NICK - June 19, 2017

this does not work:

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

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

Reply
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?

Reply
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?

Reply
Abhishek Mishra - May 12, 2017

Hello

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

Regards
Abhishek

Reply
Lorand - May 3, 2017

Hi,

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?

Thanks

Reply
    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.

    Reply
taza - April 5, 2017

hi,
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?

Reply
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.

Reply
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

Reply
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

Regards,

Tony

Reply
    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!

    Reply
      Aditi - April 25, 2017

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

      Reply
      boa - June 29, 2017

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

      Reply
Asif - March 15, 2017

Hi

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

Reply
salvatore - March 6, 2017

I refreshed page and all was well. Thanks

Reply
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

Reply
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.

Thanks
Shamna H

Reply
Nandini - February 22, 2017

Hi,
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.

Reply
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.

Reply
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!

Thanks,

Barry

Reply
    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.

    Reply
Vanessa - January 18, 2017

Hi,

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.

Reply
    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.

    Reply
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!

Thanks
Umesh

Reply
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?

Reply
    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?

    Reply
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

Ricardo

Reply
    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.

    Reply
Kannan.G - December 6, 2016

Hi
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.

Thanks.

Reply
ashok - November 25, 2016

Hi,

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”

Thanks

Reply

Leave a Reply: