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


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

  • Hi Jon

    I’m trying to copy the data from sheet 2 (example:A5:AD10) to Sheet 1, and then sheet 3 (example:A5:AD6), total 5 sheets to go.
    The number of row is unknown, its depends on how much the user input.
    And A1:AD5 for each sheet is fixed as header, so i just want it to select from A5 to the last cell(but not empty cell) and copy to sheet 1.

    I tried the code as below:

    ‘Copy from Sheet 2 to Sheet 1

    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Worksheets(“Sheet1”).Selection.End(xlDown).Offset(1, 0).Range(“A1”)

    On Error Resume Next
    ‘Copy from Sheet 3 to Sheet 1

    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Worksheets(“Sheet1”).Selection.End(xlDown).Offset(1, 0).Range(“A1”)

    On Error Resume Next

    It works only if there are more than one rows, but if there are only one row, it will become duplicated rows on sheet 1 (Example:Copy Row of sheet 3 A5:AD5 to sheet 1, it will become two line in sheet 1)

    How i explain well….Please help 🙁

  • I want to copy a range say A1:B1 from a total range of A1:B10 till it reaches blanks one by one and paste it horizontally in cells A21:A22 and again copy a range from A21:B30 and paste it to different locations say AA1 for A1:B1, AF1 for A2:B2 and so on

  • Hi Jon,
    I need to copy data from one table column to another in the same table, (table is filtered) and when it paste, values don’t match. How can I fix it? I hope you can help me, thanks.
    here is what im doing:

    wsIPV.Range(“tIPV[test]”).PasteSpecial Paste:=xlPasteValues

    in the same row, after runing the macro, both columns should have the same value, but it is not.
    any idea why?

    ws is worksheet
    tIPV is a table(ListObject)

    • Hi Barbara,
      Excel cannot perform the paste on a filtered range. This typically results in an error. My Paste Buddy Add-in has a feature called Paste Visible that solves this issue and performs the paste. I have a Developer Version of Paste Buddy available where you can access the VBA code that makes this all work. There are also other sites that have the code for this specific task of pasting to visible rows. You basically have to loop through each cell in the visible range to determine if it is visible, then perform the pastes individually. It is a labor intensive process, and this is why Excel does not allow it natively in the app. I hope that helps.

      • Thanks Jon for your reply. I had to keep moving so what I did at the end was to remove the filter, copy and paste, and then apply the filter again.

  • Hi Jon,

    Wonderful training, information and resources…thank you very much! Similar to David Briggs’ comment on July 12, 2015, I, too, “recorded” a macro to accomplish a copy/paste task. I was really happy to discover your much simpler, shorter code for this task. I chose “Option #2” for pasting between workbooks that you teach and it works great for me…up to approximately 32,000 rows. After 32,000 rows, I get an Run-time error 6: Overflow. After much research on the Internet, it seems that this results from some parameters/restrictions that exist in Excel. Apparently, using “As Long” language in the coding might be able to help, but I’m much too inexperienced with VBA to know how to implement that language into the “Option #2” code that you shared. Essentially, I have a workbook that holds a bunch of data that changes daily (interest rate indexes, etc.). After the daily update of the data, I then copy this data (cells A1:BZ46000) to a separate workbook that I use. (The first workbook acts somewhat like what I would call a “lookup database”.)

    Any thoughts you might be willing to offer on how to solve my overflow error 6 would be greatly appreciated! Thank you!

      • Thanks for your consideration of my inquiry! I thought I would share the code that I have at this point for the process that I’m trying to accomplish:

        Sub UpdateLookup()

        ‘ UpdateLookup Macro

        Worksheets(“Lookup”).Activate – THIS IS IN THE TARGET WORKBOOK
        Worksheets(“Lookup”).Range(“A1:BZ23000”).Value = Workbooks(“Master Lookup.xlsm”).Worksheets(“Lookup”).Range(“A1:BZ23000”).Value – THIS IS “COPY AND PASTE” TO TARGET WORKBOOK FROM SOURCE WORKBOOK
        Worksheets(“Index”).Activate – TAKES USER BACK TO “HOME PAGE” IN TARGET WORKBOOK
        End Sub

        I get the overflow error 6 when I change the cell references in the code to A1:BZ46000.

        Thanks again!


  • hey i want to copy data from one workbook1 to workbook2, but query is i want to copy more data to workbook2 and i want it to paste it after that data…
    eg: i want to copy 1st 10 record from workbook1-worksheet(sheet1) to workbook2-worksheet(data) and again i want to copy more records from workbook1-worksheet(sheet2) to workbook2-worksheet(data)… but paste that 10 records after the 1st records… what should be the exact code for it….?
    regards harshad.

  • Hello.

    I want to know that if I copy and paste the data of the specified name like I want to sort all the information by name ali and paste all the data of ali to another sheet or workbook so what will be the code.


  • hai sir, i need your help.
    i have 5 sheets.
    i need to copy a1:e10 then paste to a2 each sheet with one vba button.
    how the vba? sorry fo my bad english

  • Hi Jon,

    Thanks for these easy tips, they’re helping me out a lot! One question I’m wondering if you could help me with – I would love to be able to use the below code but then paste as values instead of as formulas in the worksheet called ‘Color Forecast’. I want to be able to make the ‘Color Forecast’ tab of my worksheet a copy of the values in ‘DI Entry’ tab but without the formulas present in the DI tab…

    Sub Copy_Data_to_Color_Forecast_Tab()

    Worksheets(“DI Entry”).Range(“C6:EP200000”).Copy Worksheets(“Color Forecast”).Range(“A6:EN200000”)

    End Sub

    Please let me know if you have a tip! I am unable to view the videos for some reason, so apologies if the answer is in there.

    Thank you!

    • Hi Lauren,

      Thanks for your question. The videos are on Youtube and might be blocked at your office.

      You can use either the PasteSpecial method or the .Values = .Values method for this. The Copy method will only do a straight copy/paste. To paste values you will need to use one of the other methods explained above. I hope that helps. 🙂

  • Please help

    I have 2 cell with all my final result and date.
    But the result date will change daily.
    I need to copy the result in the cell to another cell with date like daily result backup.

    Thanks in advance

    • Hi Shibu,

      You can use the PasteSpecial method to paste values. This will paste the date that is returned by the formula, and not change when the workbook is calculated. I hope that helps.

  • Hi Jon,

    Below is my working code. But while copying and pasting its paste only formula instead of value. Could you please help me in rectifying error.

    Sub Copy_Temp()

    Dim xlSel As Excel.Range

    Set xlSel = Excel.Application.Selection
    xlSel.Copy Excel.Application.Sheets(“Template”).Range(“A1”)

    End Sub


  • Hi Jon,

    I’m new to vba so please excuse me if I’m asking a stupid question.

    So I have two questions and I’m hoping you can help me.

    First question, I would like to copy a value from a cell let’s A2 and paste it cells K7:??.

    ?? means that populate this value from K7 to whenever data ends from column A in column 7.

    Second question, can I execute this scripts from a batch file?

    Thank you so very much Jon,

    Have a blessed day.

    Zulf Ansari

  • Hi mr jon
    I have a workbook of 2 sheets
    When i click the commande button to add data from sheet to another
    Its always replace the old one
    Please. Can i get the code that add my new data to the first erow in sheet2
    Sheet 1 range(“A7:B34”)
    Sheet 2 range(“B2”)

  • Hi Jon,

    Im trying to copy data from worsksheet 2 into worksheet 1 .

    And I want macro to be able to copy all the columns in worksheet 2 with the exact number of rows as the coloumn D ( meaning i want to copy all the other columns with data to the reference to column D’s last row count)

    i have something like this:
    lastRow = Range(“D” & Rows.Count).End(xlUp).Row

    I do not know how to completely code that.

    Help me please

    • Hi Yaash,

      Once you have the lastRow variable set, you can use it in the Range property to create a range reference.

      Range(“A1:C” & lastRow)

      If the lastRow was 100, then that would evaluate to Range(“A1:C100”)

      I hope that helps get you started.

  • Hay,
    I am Employer in health sector, i having a problem that i want to copy r range of cells in that some contents are colored with red fonts, expect red font row i want to copy them to another sheet called BRS using VBA ?

    • Hi Peeraji,

      One way to approach that is to filter the column for the font color, then copy/paste the visible cells. I don’t have an article that explains that in detail, but the macro recorder should help give you some of the code. I hope that helps get you started.

  • Hi Jon,
    I am working on a basic macro to copy a cell text if a criteria is met.
    This is what I have, but when when I execute all my cells are replaced…
    colume SS has text “PARENT TO BE IDENTIFIED”, I want the macro to replace ONLY the cells that show “PARENT TO BE IDENTIFIED” with the value of the cell from R column.

    For Each R In Intersect(ActiveSheet.UsedRange, Range(“s:s”))
    Range(“R2:R10”).Copy Range(“S2:S10”)
    End If
    Next R

    End Sub

    hope you can help me resolve this one.
    Kindest regard

    • Hi PJ,
      Try replacing Range(“R2:R10”).Copy Range(“S2:S10”) with the following.

      R.Value = R.Offset(0, -1).Value

      That will replace the value/text in R with value from 1 column to the left. The Offset property allows us to specify columns to the left/right/above/below. I hope that helps.

  • HI,

    i want a file template , formula or VBA code for following

    if any value change in cell A1 that value should be copy and paste in B2
    if again value change of A1 the that value shoud copy in new cell without erasing previous number

  • Hi,

    I have question,

    How should I copy last row to another file but the file is same on the drive? I don’t want to open file, it should be automatically and I don’t know how should I do it?


    • Hi Greg,
      I don’t believe there is a way to update/modify the destination file without opening it. You can change the name of the source file with a SaveAs, open the destination file, then change the name back on the source file. You can write this into the macro, and that way you can have both files open at the same time. I hope that helps.

  • Hi there,

    Thank you so much for posting these. I have code I am using in order to pull a range of values over from several worksheets in to a master (Summary) worksheet. The code works fine but the range of cells it is pulling over have formulas in them. I don’t want the formula pulled over (as it is currently doing) I just want the cell value to be pulled over. I am unsure however, how to adjust my current code. This is what I’m working with:

    Sub SummurizeSheets()
    Dim ws As Worksheet

    Application.ScreenUpdating = False

    For Each ws In Worksheets
    If ws.Name “Summary” Then
    ActiveSheet.Paste Range(“A65536”).End(xlUp).Offset(1, 0)
    End If
    Next ws
    End Sub

    Any help would be really appreciated! Thanks!

  • Hi Jon, I am working on something where I copy a row from sheet 1 and paste it in sheet 2 based on the no of people who have worked on it. I have used the above code and rows got pasted in sheet 2. I need help where the rows should be pasted based on the no. of people who have worked on it. if the no. of people who have worked cell has 3 names then the row should be pasted 3 times with each row having only one name.

    Uploaded the file in Google docs for reference.

    Thanks for your help.

  • Hi Jon,
    This is my first day with VBA macros and getting the 1004 runtime error while copying data from one workbook to another.

    Sub CopyData()

    ‘clear any thing on clipboard to maximize available memory
    Application.CutCopyMode = False

    Workbooks(“L3 SR Tickets.xlsm”).Sheets(“L3 SR Tickets”).Range(Cells(2, 2), Cells(1000, 2)).Copy
    Workbooks(“L3 status report 25thJuly16 to 31thJuly16.xlsm”).Sheets(“ServiceRequest”).Range(“B2”).PasteSpecial

    ‘clear any thing on clipboard to maximize available memory
    Application.CutCopyMode = False

    End Sub

    The same code runs fine if I try to copy lesser (around 500) cells at a time.
    But my requirement is to copy large data.

    Please help.

    Nidhi Gupta.

  • Hi Jon,

    Trying to figure out a macro that would help me at work. Here is my example: Basically I have 2 worksheets opened, I want to copy from worksheet1 cell a1,b1 to worksheet2 cell a2,a3 and basically need it to copy for the next 100 rows, so the macro would then copy worksheet1 cell a2,b2 to worksheet2 cell a4,a5 and so on.


    • Hi Dan,
      Great question! There are a few ways you could go about that. You can use the Transpose option of the PasteSpecial method to transpose the rows to columns. You would put this code in a loop to offset 2 rows from the last pasted row each time.

      You could also copy/paste the entire column, add an index column that contains numbers in sequential order for each paste range, then sort the index column.

      I will add this to my list of blog posts to create in the future. I hope that helps get you started. Thanks!

  • Dear Jon,

    Here’s my excel problem. It’s kind of a magic if I may say.

    I’m working in sheet 1, I need that the contents of Sheet 1 be automatically copied to maybe Sheet 2 or any section in Sheet 1 that I may wish it be copied. The copied items should not be carrying the formula meaning it should be a plain text and values. I know the copy paste and or special, I know the equal sign (=) in a cell then go to the cell to be copied, but I won’t choose these. Please help. Thank you.

      • Hi Jon,

        Well, I’d like the copying to be simultaneous. The copy paste and or special can be done after you have entered the data, while the one using the equal sign it can be established as a formula so you are able to copy not a text data bec. it has the formula.

        If you please, kindly take a look at this:

        I’ve researched another way of copying text data which is: In excel Sheet1, hold the ctrl key then press sheet2 or sheet3. Text entered in sheet1, ex. Rob in “A1” will automatically be copied in Sheet2 & 3 at the same col and row that is A1.

        Now what I need is, Rob can be copied to any where (row & col) I want in Sheet1, 2, & 3. Please help.

        Thank you,


  • Hi Jon i copyed your code from your and tried it.
    But i get a run-time error 9 Subscript out of range.

    Here is the code that i’m using

    Sub Pastespecial_excample()

    ‘Copy and PasteSpecial between workbooks
    Workbooks(“Book3.xlsx”).Worksheets(“Sheet1”).Range(“A1”).PasteSpecial Paste:=xlPasteFormats

    End Sub

    I hope for a quick reply and a an ansver i have a bigger program where i am trying to get data from a different book from a specific plas e.g.A3:AA150, and to paste it to a different sheet but i get the same error as this simple code that i have posted here

    • Hi Eero,

      In this case, both Book1 and Book3 will need to be open before running the macro. The files will have to be saved with these exact names. I hope that helps. Let me know if you have questions.

  • Hello John

    I am working with big set of data, so am trying to pull the data from other workbook bypassing the clipboard to save resources.

    This is part of a endless loop that i have in order to keep a dashboard updated, i tried and worked, but i notice that after a few cycles the data does not update
    any idea why?

    there is another method that could help me get the same results?

    Workbooks(“Dashboard (005).Xlsm”).Sheets(“Data Source”).Range(“A:Q”).Value = Workbooks(“New Data”.xls”).Sheets(“New Data Tab”).Range(“A:Q”).Value

  • Hello Jon,

    I am trying to paste the value of changing random numbers thousand times as a part of simulation. I have calculated a mean using random numbers and every time it gives a unique value. I want this unique value to be pasted thousand times.

  • Hi Jon

    this is my first attempt to use VBA code and i have watched your videos but need more guidance.

    I have a master sheet with multiple data per client in a row. i would like to create separate client works sheet copying the date from the master sheet into various columns, description, value(formula), order value9formula) and order cycle. Please assist with a suggested code or way forward.



  • Hi Jon,

    Thank you for this explanation which helped me tremendously with a past work. However, this time, I face a challenge that leaves me without a solution…yet! What I hope to achieve is my macro would find a cell (based on another cell input, that I was able to do), autofilter that column based on another cell (again, I was able to do that) and then, copy column A + my selection’s column to a new workbook. I tried with the union function without luck thus far.

    Thank you in advance for your answer.

  • Hello Jon,

    I hope you are doing well. I really appreciate all the videos you have been doing since they are of great help! I was just wondering if you could give me some advice, too. I am just trying to copy one column from one Workbook, and paste it to a column in another Workbook. Both Workbooks are saved in the same folder in the same computer. Here is the code I have been using:

    Sub ImportData()

    Dim document1, document2 As String

    document1 = “IMA-1.xlsx”
    document2 = “IMA-2.xlsx”

    ‘Open first workbook
    Workbooks.Open “C:\filepath\” & document1

    ‘Copy the column

    ‘Activate document 2
    Workbooks(document2).Worksheets(“Sheet2”).Range(“N9:N300”).PasteSpecial Paste:=xlPasteFormats

    Application.CutCopyMode = False

    ‘Save document1 and close
    Workbooks(document1).Close SaveChanges:=False

    End Sub

    I am using exactly what you said we were supposed to use, but for some reason the code is not doing what it is supposed to be doing. It opens document1, but that’s it….It doesn’t do the rest of the code. It doesn’t trigger any error.

    What should I do?




  • Thank you so much for these helpful videos!

    I am trying to do multiple rounds of copy:paste – first from A1:A3 to A4:A6, then A1:A3 to B4:B6, then A1:A3 to C4:C6, etc. so that eventually, as I enter different values into A1:A3 and run the macro, I get a large table of all my different values. Any advice on how to do this?

  • Hi Jon,

    I keep on encountering a run-time error 9 in the last two lines of the code. Any idea why that would happen

    Sub getData()
    Dim currentDay As String, currentMonth As String, currentYear As String, fileDate As String, workbookOne As String, workbookTwo As String
    currentDay = (Day(Date))
    currentMonth = UCase((MonthName(Month(Date))))
    currentYear = (Year(Date))
    fileDate = “(” + currentDay + ” ” + currentMonth + ” ” + currentYear + “)”
    workbookOne = “Import Non Import Summary” + fileDate
    workbookTwo = “ZICO & Monster Tracking Summary” + fileDate

    Workbooks(“DailyTemplate_SG_v2 1_08Jun_2016”).Worksheets(“Summary”).Range(“C21”, “I23”).Value _
    = Workbooks(“workbookOne”).Worksheets(“Sheet1”).Range(“C3”, “I5”).Value

    End Sub

    • Hi Pierce,
      The Workbooks references need to have the file extension in them.

      Workbooks(“DailyTemplate_SG_v2 1_08Jun_2016.xlsx”) or Workbooks(“DailyTemplate_SG_v2 1_08Jun_2016.xlsm”)

      I hope that helps.

  • Hi Jon

    My Query

    1. Have an excel sheet with data
    2. Need to copy the xl data from the sheet1 of the xl book, and mail it using macros
    3. Need it simple, and preferred object for mail is outlook

    Thanks in advance


  • I have entered your Paste value code into my work book and it is working well, but I would like it to automatically run when ever the cell in questions, in this case cells AC53:AC60 changes rather than run the Macro manually. Is there any way of doing this?

    Option Explicit
    Sub Paste_Values_Examples()
    Range(“AD52”).Value = Range(“AC52”).Value
    Range(“AD53:AD60”).Value = Range(“AC53:AC60”).Value
    End Sub

    • Hi Mark,

      Yes, that can be done with worksheet events. Macros can be run when certain actions are taken in the worksheet by the user. In this case we will want to use the Worksheet_Change event. I have a tutorial on this in my VBA Pro Course, and I will try to write up an article on it. Here is an article from Contextures that explains the Change Event.

  • Dear Jon,

    Could you please help me with following macro.

    I am looking for a macro which can copy data from A1 of Sheet 1 to B1 of sheet 2.then next A2 of Sheet 1 to B2 of sheet 2 and so on.

    Could you please help me with this.

    Appreciate your help in this regards.


  • sir
    i have a issue i want to copy my b7 e20 e21 data to other workbook d:\ram\esh\2.xlxs
    sheet 2
    thanks in advance please reply me here or send me email i am waiting

  • hi guys i need support for this line Sheet11.Cells(j, 1).PasteSpecial x1pastevalue i don’t know what is wrong

    my full command is

    Sub Macro1()
    Dim myfile As String
    Dim n As Integer

    ‘ file t be executed
    n = 30

    myfile = “D:\mm.txt”
    Open myfile For Output As #1

    For i = 2 To n + 1

    Sheet1.Cells(2, 1).Value = Sheet10.Cells(i, 1).Value

    For j = 12 To 337
    Sheet1.Cells(j, 1).Select
    Sheet11.Cells(j, 1).PasteSpecial x1pastevalue

    Write #1, Sheet11.Cells(j, 1).Value

    Next j

    Next i

    Close #1

    • Hi Amani,

      The paste enumeration is XL not X1. I know it is hard to differentiate the “1” and the “l” with a lot of different fonts. Here is a list of the pastespecial method paste type enumerations on the MSDN help site. I hope that helps.

  • Dear Jon,

    thanks for the well structured video for copying and pasting in VBA.

    I have a MacPro 2010 (so assume that Excel is 2010) and am having trouble executing the first easy command:

    ‘The Range.Copy Method – Copy & Paste with 1 line
    Range(“A1”).Copy Range(“C1”)

    You say that you can run the command by pressing F8. I also notice a yellow marker. I keep pushing F8 and nothing happens.

    Can you help me?


    Berlin / Germany

    • Hi David,

      The F8 keyboard shortcut equivalent for stepping through code on the Mac is Cmd+Shift+i. I believe you have the Mac 2011 version of Excel. Try it out and let me know. Thanks!

  • I tried the below script but it only copied the value into sheet2 but not the color that I set on sheet1.

    ‘Copy and PasteSpecial a between worksheets

    Worksheets(“Sheet2”).Range(“A2”).PasteSpecial Paste:=xlPasteFormulas

    I also tried the below and the color format was copied to c1 but not the value of a1 .
    Range(“c1”).PasteSpecial Paste:=xlPasteFormats

    thanks for your help

    • Hi Sam,
      That is correct. You are using the PasteSpecial options to paste the formulas. This only pastes the formulas or values, and does not paste formatting. PasteFormats only pastes the formats and does not paste values or formulas. If you want to paste everything then just leave the Paste argument out.


      That code will paste the formulas/values and the formatting. I hope that helps.

  • Hi John,

    Great videos, thanks for making them. I was wondering if you could give me some advice?
    I’m trying to write some VBA to populate cells in the following way.
    Starting at cell A1, populating through to A25 with the number 1, then from cell A26 populate the next 25 cells with 2, all the way up to 25 000. So in essence create 50 000 groups of 25 cells, with each set of cells increasing by 1, ending up with 652 000 rows.

    I don’t expect a full answer, maybe just a nudge in the rite direction.

    Thanks very much,

    • Hi Brad,
      Sorry to not get back to you sooner. Yes, I can help with that. We can use a For Next Loop to create the sequence of numbers. Here is the code:

      Sub Fill_Sequence()
      Dim lRow As Long
          For lRow = 1 To 25000
              Range("A1").Offset((lRow - 1) * 25).Resize(25, 1).Value = lRow
          Next lRow
      End Sub

      This code will loop through the numbers 1 to 25000. It uses the Offset property to offset 25 rows each time in the loop. It also uses the Resize property to resize the range to 25 rows tall. Then it sets the value of the 25 cells in that range equal to the current number of the variable lRow in the loop.

      I have a free video series on getting started with Macros & VBA that explains loops in more detail.

      I hope that helps. Let me know if you have any questions.

  • Jon,

    In regards to my previous post, you mention in the first video that there is no need to do the selection like the record macro does, however, is this necessary if you want to cut the selected range and not just copy it?


  • Jon,
    Thank you for your free and clear video tutorials. Do you have a recommendation on another video that explains how to do the copy/paste in a loop? I have to do the same sort of thing, repetitively and want to know if there is a way to loop the action instead of specifying each copy range and paste range?


    • Hi Elizabeth,
      Great question. You can definitely use a loop to automate the copy/paste. The setup of the loop will depend on what you want to loop through to change the range references for the copy paste. Do you want to loop through rows, columns, or specific ranges? Let me know and I will provide more help. Thanks again!

  • Hi Jon,

    i have summary data in one excel file. Example “Data1” contains some variables and numbers and “Data2” contains some details.

    Based on the data numbers, separate excel files needs to create with the details available in summary file using copy and paste values.

    Could you help with the coding for these.

    • Hi Satish,

      I’m not sure I fully understand your question. The code sample above contains an example of how to copy and paste between workbooks. Is that what you are looking for?

  • Workbooks(“Book2.xlsx”).Worksheets(“Sheet1”).Range(“A1”).Value = _

    How would you use the above with a named range. I’ve used it as per above but it only copies the first cell in the range into A1 and not the whole named range.

    • Hi James,
      For that line of code, the destination range will have to be the same size (rows and columns) as the named range. You are specifying “A1” as the destination. That means that only the value in A1 will be changed. So, A1 needs to be changed to reference a range that is the same number of rows and columns as the named range. You could use something like the following code for that.

      lRow = Workbooks(“Book1.xlsx”).Worksheets(“Sheet1”).Range(“NamedRange”).Rows.Count
      lCol = Workbooks(“Book1.xlsx”).Worksheets(“Sheet1”).Range(“NamedRange”).Columns.Count

      Workbooks(“Book2.xlsx”).Worksheets(“Sheet1”).Range(“A1”).Resize(lRow, lCol).Value

      I hope that helps. Thanks!

  • Hi Jon,

    I am trying to copy and paste the same data range on multiple worksheets onto a single summary tab. Do you have any tips on how to accomplish that?


    • Hi Brian,

      Great question! I actually have a free video series on macros and VBA, and we cover that exact scenario in the 2nd video. Here is the link to signup, it’s free.

      Basically you will want to use a Loop to loop through each sheet, copy the data/range, then paste to the summary sheet. I cover it step-by-step in video #2 of that series and also provide an example file so you can use the code and modify it. Check it out and let me know if you have any questions. Thanks again!

  • Hi Jon
    in the Paste_Values_Examples() above, does one reference the path location within the “”

    Workbooks(“C:\Source\Book1.xlsx”).Worksheets(“Sheet1”).Range(“A1”).Copy _


    • Hi Graham,
      Great question! The Workbooks Property represents a collection of all the open workbooks on the computer. So you only need to reference the workbook name, and not the file path. The following is the correct code.


      If the workbooks are not open then you will need to add lines of code above that to first open the workbook. The following line uses the Workbooks.Open method to open the workbook.

      Workbooks.Open “C:\Source\Book1.xlsx”

      I hope that helps. Please let me know if you have any questions. Thanks!

  • I never cease to be amazed at what I don’t know.
    I’ve created some powerful automated spreadsheets using VBA. For example, I created some VBA code that uses the LINEST and SERIESSUM functions to build predictive gas usage models based on historic daily natural gas consumption and temperature data. Unfortunately, I initially learned VBA coding by reviewing the code created by the Macro Recorder. I’m sure you’re aware how much code is created by the recorder just to copy and paste data from one workbook to another. It’s sad to say, but I had no idea it could be as simple as what you’ve shown here. I look forward to revising my code.

    • Thanks David! I completely agree with learning new things. I don’t know if I’ve ever used the SERIESSUM function. There is always something new to learn with Excel and VBA and I think that is what makes it fun. Thanks again and have a great day!

Generic filters
Exact matches only
Filter by Custom Post Type


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