158

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 158 comments
Bjarne Dreier - October 3, 2018

Hi, I am trying use the above to join values into the same cell. It works fine for one row, but as soon as I try with multiple rows it fails.
Any ideas?

Works: Range(“A2”).Value = Range(“A2”).Value & “” & Range(“P2”).Value
Fails: Range(“A2:A6”).Value = Range(“A2:A6”).Value & “” & Range(“P2:P6”).Value

Thank you

Reply
Donald - August 1, 2018

Hello. Thanks for being available.

I have a workbook with tabs labeled 8.1.18 MN, 8.1.18 AM, 8.1.18 PM and so on for the entire month. I have a tab named Sheet1 where I have the above in a list – I used this list to rename all the tabs at once – ending at 8.31.18 PM – I would like to know how I can use this same list and import them in much the same way as the tabs, only placing the data from Sheet1 into a cell on every sheet, the cell I want the data to go is B3.

I don’t know how to have excel do this in much the same way it was able to rename all the tabs correctly using A1 to A93 on Sheet1 – can you help?

Reply
JP - June 28, 2018

Thanks for this. Very helpful.
Is there a way to create a new “tmp” directory in a specified path that the pdf’s can be dumped into? My issue is the generated pdf’s have to have a very specific file names or a specific order that they all get combined into for a single pdf. Best to do it all in tmp. Have to do this every week hence want to automate

Reply
Logeswaran - June 27, 2018

How to use if function in VBA?

Reply
rose - June 15, 2018

Sub CopyTransfer_Data()
‘Copy and Paste to another worksheets as Range
Worksheets(“State Legal”).Range(“I”).Copy Worksheets(“Analysis”).Range(“CR”)
Worksheets(“State Legal”).Range(“J”).Copy Worksheets(“Analysis”).Range(“CS”)
Worksheets(“State Legal”).Range(“U”).Copy Worksheets(“Analysis”).Range(“CU”)
Worksheets(“State Legal”).Range(“S”).Copy Worksheets(“Analysis”).Range(“CX”)

‘Copy and Paste to another worksheets as Range
Worksheets(“S.Lien-Ins”).Range(“B”).Copy Worksheets(“Analysis”).Range(“CV”)
Worksheets(“S.Lien-Ins”).Range(“C”).Copy Worksheets(“Analysis”).Range(“CW”)
Worksheets(“S.Lien-Ins”).Range(“D”).Copy Worksheets(“Analysis”).Range(“CT”)
Worksheets(“S.Lien-Ins”).Range(“A”).Copy Worksheets(“Analysis”).Range(“E”)

‘Copy and Paste to another worksheets as Range
Worksheets(“County Data”).Range(“C”).Copy Worksheets(“Analysis”).Range(“CY”)
Worksheets(“County Data”).Range(“C”).Copy Worksheets(“Analysis”).Range(“CY”)
Worksheets(“County Data”).Range(“E”).Copy Worksheets(“Analysis”).Range(“DA”)

‘Copy and Paste to another worksheets as Range
Worksheets(“Sample”).Range(“T”).Copy Worksheets(“ANalysis”).Range(“DF”)
Worksheets(“Sample”).Range(“U”).Copy Worksheets(“Analysis”).Range(“DG”)

End Sub

======
is there something wrong with my equation for VBA ?? I keep having an error message like “Run Time Error ‘9’”, Subscript out of range.

Reply
    Alphonse - June 29, 2018

    I will imagine my answer comes a bit too late, however, for the benefit of others…

    “Runtime error 9 Subscript out of range” usually appears when we have mistyped the name of a sheet or the sheet we are calling does not exist.

    A couple of things I noticed… OK, three…:
    – If none of your worksheets are Chart sheets, you could shorten the code by using Sheets instead of Worksheets.
    – For the County Data sheet, you repeated the second line, …Range(“C”).Copy … Range(“CY”)
    – If you need to separate the lines for ease of readability and/or editing, try placing a space and an underscore after the word Copy, as shown below:

    Worksheets(“State Legal”).Range(“I”).Copy _
    Worksheets(“Analysis”).Range(“CR”)

    (I usually further indent my separated lines with 4 spaces to ease identifying them. Well, I only indent 2 spaces in my regular code, so 4 I notice easily)

    CHEERS

    Reply
Andrew Petrakis - April 11, 2018

I am really green to VBA and macros. I have been working on a 2 tab excel sheet. Sheet 1 is a row by row listing of a specialized part with all data about how it performed. Each line in that row is a different serial number. When that part has lasted it’s life cycle the technician will fill out all pertaining cells in that row that apply for that part.

The second tab is a form that I made in excel that would go with the expired part. There are 35 cells that would be filled in this form.

I would like to take the data from the cells in the row in sheet 1 and send it to the cells in the form I made in sheet 2.

There are some cells in sheet one that are never used for sheet 2.

I was thinking that using a transfer button for each row would work but I am not sure weather this would work. Sheet 1 would be used for the same part but different serial numbers. Sheet 2 would be used only when the part is being removed and returned with this form.

Any help would be so appreciated.

Reply
    Jon Acampora - April 12, 2018

    Hey Andrew,
    Great question! There are different ways to go about this. You could use lookup formulas like VLOOKUP to pull the data into the sheet 2 form, then use VBA to make a copy of the sheet, print the sheet, export to pdf, or whatever you need to do with it.

    It might be easiest to use lookup formulas to populate sheet 2, then use VBA for the rest of the process. If the layout of sheet 2 changes, it will be easier to update the formulas instead of modifying a lot of code.

    I also have a free video series on the lookup formulas that will help if you are not familiar with Vlookup and Index/Match.

    I hope that helps.

    Reply
Dhanya - April 9, 2018

Hello,

I need to do an excel mapping. Copy data from column BG from sheet 1 to column A in sheet 2, sheet1.column A to Sheet2. column B, sheet1. column N to sheet2. column C.

Please help me on this.

Reply
Andro - April 2, 2018

Hi Jon,

Need a help on the below coding.

Rather than opening the workbook couple of time. would like to pull the data from cell E3 and E5(might be D6 etc) and paste it in Tranpose ie A1 and B1 (output)

Set bookList = Workbooks.Open(everyObj)
Range(“E3”).Copy
ThisWorkbook.Worksheets(“sheet1”).Activate
Range(“B100”).End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close

Set bookList = Workbooks.Open(everyObj)
Range(“E5”).Copy
ThisWorkbook.Worksheets(“sheet1”).Activate
Range(“A100”).End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close

Regards
Andro M

Reply
Madeline - March 22, 2018

Hello,

I am looking to copy text then paste value lower in the spread sheet. When pasted it needs to offset to the next empty row.

Please help. Below is my current macro, but I need it to paste a value.

Sub FacialTissue()

Range(“$B$7:$H$7”).Copy Destination:=Range(“C” & Rows.Count).End(xlUp).Offset(1)

End Sub

Reply
Shivali - March 13, 2018

Hi Jon,

I have a list of data in Sheet 2 range[A2:A10]. I have to copy this data in Sheet1 Cell [A2] on Submit click one by one . For eg: When I click on Submit Sheet2 cell[A2] is copied on Sheet1 Cell[A2]. Again when i click on Submit Sheet2 Cell[A3] is copied in Sheet1 Cell[A2] and so on.

Please suggest on this.

Thanks

Reply
Kim - March 6, 2018

Hello.

I want to create copy of a current template called Profile, which has reference to data to another sheet. When a new page is created from this template, I want the data in the reference cell to move down by 1 row (for example, A3 +1).

Code to duplicate the template.

‘Duplicate Profile sheet x times
Sub Copier()
Dim x As Integer
x = InputBox(“Enter number of times to copy Profile”)
For numtimes = 1 To x
ActiveWorkbook.Sheets(“Profile”).Copy _
After:=ActiveWorkbook.Sheets(“Profile”)
Next
End Sub

Do you have any suggestion? Thank you.

Reply
J - February 28, 2018

Last Column copy made simple.
This is my working macro – but it is very slow, and I’m trying to utilize some of your other videos so that I can speed them up:
Sheets(“Sheet1”).Range(“B31:B32”).Copy
Sheets(“Sheet2”).Range(“AG27”).End(xlToLeft).Offset(, 1).PasteSpecial xlValues
Application.CutCopyMode = False

I’m trying to use:
Sheets(“Sheet2”).Range(“AG27”).End(xlToLeft).Offset(, 1).Value = Sheets(“Sheet1”).Range(“B31:B32”).Value

I can get it to copy the top cell “B31” but it won’t copy over the other cells. Even when I specify the range to copy to: Below does not work:
Sheets(“Sheet2”).Range(“AG27:AG28”).End(xlToLeft).Offset(, 1).Value = Sheets(“Sheet1”).Range(“B31:B32”).Value

I can do it with separate sets of code, but I would like to use this elsewhere with larger ranges of cells.

Reply
    Alphonse - June 29, 2018

    Hi,
    I don’t know if you have found a solution, and actually, I don’t have a solution for the copy-paste statements. What I do have is a suggestion that might help with the processing speed. Use these two lines as shown below:

    Sub YourSub()
    Dim statements if any
    Application.ScreenUpdating = False
    … processing code here…
    Application.ScreenUpdating = True
    End Sub

    CHEERS

    Reply
Jim E - February 27, 2018

Hi Jon,
I have multiple large worksheets each with up to 50 rows of sum totals.
I want to copy the sum totals to another sheet except that the totals need to be
pasted to a vertical column.
Is this possible?

When you paste sum totals to another sheet you have to click the clipboard icon after each and select copy values.
Will this action record in the macro?

I assume that I could filter on the 50 total rows so they are all together.
Then record a keystroke macro to copy each cell to the appropriate column cell in the second worksheet.

Once I record the macro for the first row I assume I would have to copy that routine 49 more times then go through that text and edit the cell numbers.

Does this sound right or is there an easier way?
Thank you

Reply
    Alphonse - June 29, 2018

    Hi Jim,

    The extremely short answer is Yes, using

    .PasteSpecial Transpose:=True

    Without any code or actual info to go by, I would say that you should copy the max range of all rows and paste them, something like this:

    Sheets(“Source”).Range(“A1:Z1”).Copy ‘ repalce Z with the highest letter of all rows
    Sheets(“Target”).Range(“A1”).PasteSpecial Transpose:=True

    Keep in mind that there are other settings for the PasteSpecial property, like
    .PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    .PasteSpecial Paste:=xlPasteFormats
    .PasteSpecial Paste:=xlValues
    so, do look up PasteSpecial and use those that suit your needs.

    For 50 consecutive rows being pasted to 50 consecutive columns you could use a For statement… but I don’t believe it would work correctly on filtered rows.
    For i = 1 to 50 ‘ if rows start at 17, For i = 17 to 66
    Sheets(“Source”).Rows(i).Copy
    Sheets(“Target”).Columns(i).PasteSpecial Transpose:=True
    Next i

    If the rows are not consecutive, but they are always the same rows to copy, then you could use an array to paste to Consecutive Columns. You need to previously load the array with your row numbers as needed.
    For idx = 1 to 50
    Sheets(“Source”).Rows(Array(idx)).Copy
    or
    Sheets(“Source”).Rows(Array(idx – 1)).Copy ‘ if the array’s index starts at 0
    Sheets(“Target”).Columns(idx).PasteSpecial Transpose:=True
    Next i

    Cheers

    Reply
momna - January 8, 2018

hello
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
Regards
Momna

Reply
snow - December 2, 2017

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

Reply
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

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

Reply
    Jeffrey - November 28, 2017

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

    Reply
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
Else
Set rng = Union(rng, cell)
End If
End If
Next
‘delete all empty cells (with “”)
If Not rng Is Nothing Then rng.Delete Shift:=xlUp

End With
End Sub

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

Reply
    wael - January 31, 2018

    Dear Rahin,
    I try to do same what you want to do it exactly
    so if you find the code for copy from one excel file to another
    file please share it with me
    am waiting your reply
    thank you

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

Reply
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

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

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

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

CATEGORY VEHICLE PRICE

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

Reply
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
      Vijay - August 3, 2017

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

      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: