How to copy last used row to next empty row in another worksheet

< Back to Search results

 Category: [Excel]  Demo Available 

How to copy last used row to next empty row in another worksheet




The user is looking for a macro that will copy the bottom most used row to the next empty row on another worksheet (leaving a copy on the original sheet).

So far, the user has this code:

Application.ScreenUpdating = False
Dim NextRow As Range
Set NextRow = Sheet3.Cells(Cells.Rows.Count, 1).End(xlUp).Offset(1, 0)
NextRow.PasteSpecial (xlValues)
Application.CutCopyMode = False
Application.ScreenUpdating = True

Obviously this needs to be combined with some sort of "last row" command. 


Eventually the user will be copying rows from multiple sheets onto the master log, so would using the sheets title/name rather than it's number be more beneficial here?




Option Explicit

Sub CopyRow()

Dim lastrowSrc As Long
Dim lastrowDest As Long

    'Get last row of data
    lastrowSrc = Sheets("V.2").Range("A" & Rows.Count).End(xlUp).Row
    'Get first blank row (last row of data +1)
    lastrowDest = Sheets("LOG").Range("A" & Rows.Count).End(xlUp).Row + 1
    'Copy row
    Sheets("V.2").Range("A" & lastrowSrc).EntireRow.Copy Sheets("LOG").Range("A" & lastrowDest)

End Sub


Obtained from the OzGrid Help Forum.

Solution provided by rbrhodes.


See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets


See also:

How to use Excel VBA macro to convert multiple columns to multiple rows
How to sum up columns in each row and highlight until that value
How to transpose single column into multiple columns and rows
Split row into many rows using trigger in particular row cells


Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.


stars (0 Reviews)