Copy & Paste Below the Last Used Row with VBA

Thanks again for joining me!

This page contains the free bonus video from the video series on Copy & Paste with VBA.  You can download the example Excel file below to follow along with the video.

Please leave a comment at the bottom of the page with any questions.

Video: Copy & Paste Below the Last Used Row

Download the Example File

Download the example Excel file to follow along.

Copy-Paste-Below-Last-Used-Row.zipDownload

How to Copy & Paste Below the Last Used Row

In the video above I explain how to copy & paste below the last used row with a macro. There are three basic steps to this process.

Step 1 – Find the Last Used Cell in the Destination Range

The first step is to find the last used cell in the destination range.  There are a few different ways to do this.  I have a free 3-part video series on 3 ways to find the last used cell with VBA.

In the example above we use the Range.End method to find the last used cell.  We set a variable equal to the result of the Range.End method.

lRow = Worksheets("Data").Cells(Worksheets("Data").Rows.Count, 1).End(xlUp).Row

We then offset the row number by 1 to go down 1 cell from the last used cell.  This gives us a reference to the first blank cell on the sheet.

lRow = lRow + 1

Step 2 – Copy the Source Range

The next step is to copy a range of cells.  This can be a specific range every time (A2:C5).

Range("A2:A5").Copy

Or, we can use the find last method from step 1 to find the last used cell in the copy range.  The macro named “Copy_Paste_Below_Last_Cell_CopyRange” in the example file uses the Range.End method to find the last used row in the sheet we are copying data from.

Worksheets("New").Range("A2:C" & lNewRow).Copy

This means that the copy range can be a different number of rows every time we run the macro.  This works well if you are using the macro to add data to a new sheet periodically, and you're not sure how many rows of new data you will have.

Step 3 – Paste to the First Blank Row in the Destination Sheet

The last step is to paste the copied data below the last used row that we found in step 1.  We can concatenate or join the text of the column letter and the variable that contains the row number returned in step 1.

Worksheets("Data").Range("A" & lRow).PasteSpecial

There are several different ways to copy & paste data with VBA. Check out my free 3-part video series on how to copy & paste with VBA to learn how to copy/paste values, formulas, formats, etc.

Additional Resources

In the video I use the Immediate Window to evaluate a few lines of code. Check out my article on 5 ways to use the Immediate Window to learn more about this awesome feature of the VB Editor.

Please leave a comment below with any questions.

61 comments

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

  • Such a clear explanation – As a relative novice that has tirelessly searched the web for support, this is the best support that I have experienced for Excel VBA – this far.

    Thanks.

    Reply

  • Hi Jon, my data is varies everyday. i want to copy 500 data, from last row to upper row. can you help me?

    Reply

  • Just to echo Gareth’s comment and also as new user of VBA.
    I spent 3 hours searching the web for information on how to copy from one sheet to the first blank row, cell, column etc. in another sheet.
    There is a huge amount of VBA info on the web that uses various methods to perform this task, however, none of the sites I have visted do not explain the step by step process as good as it is explained here (which makes perfect sense when you visually see it).
    Excellent stuff, thank you very much.

    Reply

    • Hi Martin,
      Thank you so much for the nice feedback. I really appreciate that. You made my day! 🙂 I’m happy to hear you are enjoying the videos and learning more about VBA.

      Reply

  • Hi Jon. You have an excellent didactic to teach people like me that know little bit Excel, but not VBA at all. I have only watch 2 videos, and I am amazing how much I understood and really learned about VBA. I wish I would know about your website long time ago. I am so happy you decided to share your knowledge and help others with so much free material. Today is only my second time on your website and a second video I watched, but it is enough for me to know I want to buy your VBA Pro curse. I am done searching for some online curse I would feel confident I would be able to follow it. Thank you and Congratulations on all your great work!

    Reply

    • Hi Noemi,
      Thank you for the nice comment. I’m happy to hear you are learning more about VBA and enjoying the videos. I look forward to having you as a member of the course, and can’t wait to see what you create with VBA.

      Thanks again and have a nice day! 🙂

      Reply

  • Hello, I really find these videos helpful. I am trying to copy one line of updated information at a time into a new sheet as it is updated to kind of keep a log of information as it is changed in the main sheet. I want to use a button, so that every time I press the button, the row of information is added to the next row of the log sheet. I know how to set up the button but the code has just a little error every time I try to fix it. I do have some spaces in my information so I am trying to use the “Find” method with the “copy and paste to next row in another worksheet” code but for some reason it isn’t working out quite right.

    Here is my code (I followed your video almost exactly):

    Sub Copy_Paste_Into_Log()
    ‘Find the last used row in a sheet and copy paste data below it.

    Dim lRow As Long

    ThisWorkbook.Activate

    lRow = Worksheets(“Sheet 2”).Cells(Worksheets(“Sheet 2″).Rows.Count, 1).Find(What:=”*”, _
    After:=Range(“A1”), _
    LookIn:=xlFormulas, _
    LookAt:=xlPart, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious, _
    MatchCase:=False).Row

    lRow = lRow + 1

    Worksheets(“Sheet 1”).Range(“B3:J3”).Copy

    Worksheets(“Sheet 2”).Range(“C” & lRow).PasteSpecial

    Application.CutCopyMode = False

    End Sub

    I have labels as my headings but I don’t think that would make the error go off?

    It brings up Run-time error ’13”: Type Mismatch

    I feel like I am not integrating the “find” method properly into this copy paste method but I am unsure of where I am typing the code incorrectly.

    All help is greatly appreciated thank you.

    Reply

  • Hello Jon Acampora,

    Nice tutorials!! I wonder what software do you use to record your desktop screen?

    Thanks for the tip!

    Reply

  • Hi Jon
    Thank you for the information on a very interesting topic. All three of the ways presented by you are in a position to help in a simple case. Personally, I have used the other way so far, but the other two are also interesting.
    Regards

    Reply

  • Hi Jon
    I must agree wholeheartedly with the other respondents. Your method of explaining things are plain and straightforward.
    It is so exciting for me to learn something new every time you post something. I have learnt so much about VBA and Excel in general. (Is the house next door to you maybe up for sale!!!??)

    I was absolutely astounded when I opened up the above email to find out what you were about to discuss with us. I spent some time during the past two days doing EXACTLY this while writing a macro to automate a simple accounting statement procedure for a friend of mine. It was also necessary for me to find the next empty row in the body of the statement so that the previous months’ data could be retained. After searching the Internet, I eventually settled on the code below.

    Range(“I16:M17”).Copy Sheets(“Currie”).Range(“B” & Rows.Count).End(xlUp).Offset(1, 0)

    This seems to be working fine.
    Thank you, once again, for sharing your knowledge with the likes of us mere mortals.

    Reply

  • Jon,

    All your macros in this topic will be very useful to my every day activities. I will start applying this technique and will get back to you once I encountered any issues on my macros.

    Thank you,
    Florence

    Reply

  • Hello Jon,

    For some time I have been reading a book about how to program with VBA but some important details have eluded me. Your video presentation has made those details clear to me. Thank you for the wonderful presentation!

    Reply

  • Thanks, Jon!

    This sort of operation has bedeviled me more than once. It’s always clearer when you’ve explained it.

    Best,

    David

    Reply

  • Hello Jon,

    I always use VBA in Excel; this method ensures fast and accurate results. I enjoy learning and expand my knowledge. Learning many methods to solve a specific problem is essential and more helpful, especially in a simple, clear and understandable methods and step-by-step you use.

    After watching some of your videos I find them beneficial and helpful for me, that’s why I decided to join “Excel Campus”. I always deal with large and wide records that’s way most times I use Tables. Nowadays I start watching your tutorial videos.

    Keep working, many peoples enjoy your class.

    Reply

  • Thank you. I have been reading your newsletters regularly and have saved them all up. They have been very helpful.

    Reply

  • I want to know macro coding. To copy the data from different separate excel sheet and paste in combined excel..

    Reply

  • Jon,
    Great presentation, clear and concise. Do you have a video that shows how to fill in the blanks with the values above? Date field. Thanks

    Date Day
    6/12/17 Mon
    Mon
    Mon
    Mon
    Mon
    Mon
    Mon
    Mon
    Mon
    Mon
    Mon
    Mon
    Mon
    Mon
    Mon
    6/13/17 Tue
    Tue
    Tue
    Tue
    Tue
    Tue
    Tue
    Tue
    Tue
    Tue
    Tue
    Tue
    Tue
    Tue
    Tue

    Reply

  • I’m screwing up somewhere. Please take a look and point out where I screwed up.

    Sub Copy_Paste_Below_Last-Cell()
    Dim lRow As Long

    lRow = Worksheets(“Expenses”).Cells(Worksheets(“Expenses”).Rows.Count, 1).End(xlUp).Row
    lRow = lRow + 1
    Worksheets(“ExpenseSummary”).Range(“A2:C4”).Copy
    Worksheets(“Expenses”).Range(“A79” & lRow).PasteSpecial
    Application.CutCopyMode = False

    End Sub
    R
    Bill Barney

    Reply

  • Hello,

    Thank you for your videos; I learn so much from you.

    Why do you need to write the line of code “ThisWorkbook.Activate”? What does it do?

    Reply

  • You have a gift to teach people and put things easy. Great job! I know a little bit Excel, but not VBA at all (never had really the time to get to the bottom of it) I’m used to record macros and try go get a bit of code here and there to perform some of my tasks.
    Still I need to go around your free resources and see if I get some more explanation on the different methods and get a logic, otherwise I understand what you are doing, but not able to do it by myself – for instance, what does it really “DIM” means?

    Anyway, nice job and hope you en posting more classes!

    Reply

  • I struggle because blank cells with formula were treated as not empty. Not sure how to turn those blank formula cells into really blank cells.

    Reply

  • Jon,

    Why not simply start at cell A1 and emulate the Ctrl+Down Arrow method in Excel into the macro? Why do you have to go to the last row and then up to the first non-blank row?

    Thanks.

    Len

    Reply

    • Hi Len,
      The Ctrl+Down arrow from the top will only work if there are no blanks in the column. Otherwise it will stop at the first blank cell in the column, which might be different from the last used cell.

      Reply

  • Jon,

    In this line of code: lRow = Worksheets(“Data”).Cells(Worksheets(“Data”).Rows.Count, 1).End(xlUp).Row

    why do you have to specify Worksheets(“Data”) a second time following .Cells? Isn’t the sheet already referenced at the beginning of the line?

    Thanks.

    Len

    Reply

    • Hi Len,
      Great question! The worksheet reference does NOT apply to the parameters specified in the Cells property. Parameter values for a property or method can be considered separate lines of code that evaluate independently of the property or method they are referenced in.

      If we used the following instead, Rows.Count would count the number of rows on the ActiveSheet. 

      lRow = Worksheets(“Data”).Cells(Rows.Count, 1).End(xlUp).Row

      If the ActiveSheet is not the Data sheet, then there is potential for an error.

      In this case it’s not likely to cause an error unless your workbook has chart sheets, which don’t contain any rows.

      Otherwise, worksheets will contain the same number of rows, and Rows.Count will just return that number to the parameter of the Cells property.

      It’s good practice to always reference the sheet name for parameters unless your code has selected the Data sheet on a line above to make it the active sheet.

      Another way to shorten this is to use a With statement.

      With Worksheets(“Data”)
   lRow = .Cells(.Rows.Count, 1).End(xlUp).Row
End With

      Every line that starts with a “.” within the With Statement is a continuation of the reference after the word With. Notice that .Rows.Count starts with a “.”. This means it also uses the Worksheets(“Data”) reference before it’s first “.”.

      I hope that helps.

      Reply

  • Jon,

    Thank you for the video. I am looking for a way to save time populating a workbook. Would you please help me write some code that will accomplish the following:

    1) Copy/paste the newest row of data (in your video it takes the entire table) onto a different sheet. Going a step further, is it possible to have the data pasted onto not only one, but two sheets?
    2) Maintain consistent formatting. For example, I need the pasted data to have the same formatting as the data above it.

    Thank you very much for your time and help.

    Reply

  • Hi Jon,
    Your videos are very helpful.
    I want to copy paste a range from another workbook.
    I download approximately 1000 rows of data and want to add it to my data table.
    Do you have a suggestion?
    Thanks,
    Steve
    [email protected]

    Reply

  • what if i want to clear the selection after pasting it ? to be like that ;once i click save button in sheet1 :

    copy from sheet1
    paste in next row in sheet2
    clear selection in sheet1

    thanks for the great lessons to explain code in just simple way

    Reply

  • Hi, dear Jon,

    i want to copy data from one workbook to another without opening the source file. my macro is as below:
    Private Sub CommandButton1_Click()

    Dim scr As Workbook
    Set scr = Workbooks.Open(“G:lte counters2017_05_25.xlsx”, True, True)
    Workbooks(“lte counters2017_05_25.xlsx”).Worksheets(“nokia lte counters”).Range(“A1:C20”).Copy Workbooks(“book2”).Worksheets(“sheet1”).Range(“A1:C20”)
    Workbooks(“book2”).Worksheets(“sheet1”).Activate

    End Sub

    how shall i change it?

    thanks,
    leila

    Reply

  • I have data spread on 50 worksheets in a workbook. Each row contains different type of data.

    Goal: I would like to compile all data from 50 worksheets into a single worksheet and create separate columns for each data type.

    Question: Do you know how to do this using excel/VBA macro? If Yes then share it please.

    If you need additional info please let me know.

    Thanks.

    Reply

  • Hello! I am using this code to apply to my project however the very first line, which is meant to find the last used row on the sheet I’d like to paste into, is returning an error saying the subscript is out of Range.

    I’ve used the following code:

    Sub Enter_New_Record()
    ‘Find the last used row in Disciplinary Records and paste new record information into it.’

    Dim lRow As Long

    ThisWorkbook.Activate

    ‘1.Find last used row in Disciplinary Records’
    lRow = Worksheets(“Sheet4”).Cells(Worksheets(“Sheet4”).Rows.Count, 1).End(x1Up).Row

    ‘Offset 1 row below last used row’
    lRow = lRow + 1

    ‘2. Copy data’
    Worksheets(“Sheet1”).Range(“B21”, “B25”, “G21”, “I21”, “K21”, “M21”).Copy

    ‘3. Paste data’
    Worksheets(“Sheet4”).Range(“A” & lRow).PasteSpecial

    ‘4. Clear copy mode’
    Application.CutCopyMode = False

    ‘5. Delete info in copied cells on Disciplinary Input’

    End Sub

    Reply

  • Hello Jon,

    Thanks for the video, very useful.

    I need to tweak this a little for my needs.

    I have a column dedicated to a decreasing quantity of an item. When this column (G) gets to zero, I would like to run a macro that searches for zero in that column, then cuts that whole row and pastes it into a different worksheet in the same workbook. Does that make sense?

    What I have so far is as follows:

    Sub Find_Move_Zeros()
    ‘Find the last used row in a sheet and copy and paste data below it.

    Dim lRow As Long

    ThisWorkbook.Activate

    ‘1. Find last used row in destination sheet
    lRow = Worksheets(“Completed Dedications”).Cells(Worksheets(“Completed Dedications”).Rows.Count, 1).End(xlUp).Row

    ‘Offset 1 row below last used row
    lRow = lRow + 1

    ‘2. Copy data
    Set ws = ThisWorkbook.Worksheets(“Current Dedications”)

    LastRow = Cells(Rows.Count, “G”).End(xlUp).Row

    For i = LastRow To 1 Step -1
    If Range(“G” & i).Value = “0” Then
    Range(“G” & i).EntireRow.Cut
    Worksheets(“Completed Dedications”).Rows(lRow).Paste
    End If
    Next i

    ‘Clear copy mode (marching ants around copied range)
    Application.CutCopyMode = False
    End Sub

    It falls down on the “Paste” line before the End If.

    Any help would be greatly appreciated.

    Many thanks.

    Keith

    Reply

  • I am trying to do something very similar which is how I found your site (which I LOVE, btw).

    I have two tables. The first populates one rown at a time starting at the top with some cells that equal “” and others that have integers in them. I am trying to copy each new row and paste the values into the first row in the second table which has room for EACH populated cell in the copied row. In other words, paste into the first row that has “room” without having to voerwrite a cell that already has content.

    Any suggestions?

    Reply

    • Hi John,
      Thanks for your support! I do have some code on another article on deleting blank rows that might help you. There is a macro towards the bottom of that page that loops through the range/table and checks for entire blank rows. The If statement in that code does the checking, and looks like the following.

      If WorksheetFunction.CountA(rRow) = 0 Then

      You’ll just need to modify it to do the paste instead of the Union that create the range to select or delete. I hope that helps.

      Reply

  • Unfortunately, try as I might, I can only get it to copy and paste a single cell, despite having the range from which to copy clearly defined. I even copied and pasted the example macro and changed the arguments to fit, but it only fills a single cell whenever I run it. It is as follows:
    Rem Attribute VBA_ModuleType=VBAModule
    Option VBASupport 1
    Option Explicit

    Sub Copy_Paste_Below_Last_Cell()
    ‘Find the last used row in a sheet and copy and paste data below it.

    Dim lRow As Long

    ThisWorkbook.Activate

    ‘1. Find last used row in destination sheet
    lRow = Worksheets(“DailyFoodLog”).Cells(Worksheets(“DailyFoodLog”).Rows.Count, 1).End(xlUp).Row

    ‘Offset 1 row below last used row
    lRow = lRow + 2

    ‘2. Copy data
    Worksheets(“BlankDailyFoodLog”).Range(“A1:L45”).Copy

    ‘3. Paste data
    Worksheets(“DailyFoodLog”).Range(“A” & lRow).PasteSpecial

    ‘Clear copy mode (marching ants around copied range)
    Application.CutCopyMode = False

    End Sub

    Is there any reason this would not paste the entire range of data that I want?

    Reply

  • Hai Jon.

    awsome explanation and enjoy your video . Could you assist me . I want copy sheet1 to sheet2 with range as below

    1)Sheet 1

    Reply

  • Hai Jon

    Awsome explanation. Could you assist me . I want to Copydata input from sheet1 to sheet2 with multiple..
    Range in sheet 1 Sheet 2
    1) B2:B10 to B2:J2
    2)A12:E15 to K2:O5
    3) B16:B18 to P2:R2

    Is it VBA can do. TQ

    Reply

  • Hi Jon,

    I am a very beginner learning Macros.I have set up a purchase order form which contains PO number, Supplier information and order information. I need to copy this information to another worksheet where I have got one cell with formulas to calculate the discount.
    What I am trying to do is, every time a purchase order is completed, The information from various cells from the Purchase order form needs to be copied to the last raw of another sheet.Then the discount will be calculated by a formula.
    How can I do this by using relative reference? and also to apply end, down, down sequence

    Could you please help me?
    Thanks

    Reply

  • Hello Jon,

    I am trying to understand the count row code. You mentioned in an earlier comment about the worksheet reference and I am not clear on what you are referring to. I was able to get my code to reference the right worksheet, but only if I typed in the specific worksheet name. However the worksheet name that I would like this to work on could change based on user input and I would like the code to still count the rows so the value can be placed in other areas of my code.

    So long story short, what would you suggest is the best way to count the number of rows used on the active selected worksheet?

    Thanks,

    Reply

  • Oh… after looking in several websites, I found the solution here. I had already how to find the last row, but to paste in the target location was the trick. Thanks, Jon.

    Reply

  • run-time error ‘9’ when it reaches
    Worksheets(“Sheet1”).Range(“A1”).Copy Worksheets(“Sheet2”).Range(“A1”)

    Reply

  • How do I copy f.eks C2:C17 in one sheet into second sheet in a new (insert row below) row A-P. (From column to row).

    I have tried to modify macros, without luck, for several days now.

    Reply

  • Good morning, Mr. John
    Please provide me with the code to create a new folder.The folder path is the same as the Excel program and is automatic when you move the program.It is automatic when you transfer the program to another computer.For example, if the program is in hard disk e, the new folder is in hard disk e

    Reply

  • EXCELLENT Information, I have been using range, End(xlUp) and offset to reach the same objective. Some of these here are a little more elegant!

    Reply