How to copy last used row to next empty row in another worksheet
Category: [Excel] Demo Available
How to copy last used row to next empty row in another worksheet
Requirement:
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:
Code:
Application.ScreenUpdating = False
Dim NextRow As Range
Set NextRow = Sheet3.Cells(Cells.Rows.Count, 1).End(xlUp).Offset(1, 0)
Sheet2.Range("A19").EntireRow.Copy
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?
Solution:
Code:
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.