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

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.

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!

180 comments

Your email address will not be published. Required fields are marked *

  • Hi, could someone help adjust this formula for me? I wanted to copy a cell that is 2 cells above the selected cell, and then paste it in the selected cell.
    Thank you.

  • 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

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

  • 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

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

    • 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

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

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

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

  • 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

  • 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

  • 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

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

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

    • 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

  • 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

    • 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

  • 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

  • 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

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

  • 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

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

    • 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

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

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

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

  • 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

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

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

  • 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

  • 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

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

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

  • 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

  • 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

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

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

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

  • 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

  • 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

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

  • 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

  • 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

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

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

  • 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

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

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

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

  • 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

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

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

  • 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

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

  • 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

Search
Generic filters
Exact matches only
Filter by Custom Post Type

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly