How do I copy the last nonempty row values to another sheet using VBA?

Question

@Jeeva have you tried any of the answers you got ? any feedback ? thanks ? — Shai Rado, Dec 15 '16 at 19:00
@Jeeva really? you prefer the answer with `Select` all the time ? — Shai Rado, Dec 16 '16 at 09:29
Honestly i'm not care the way of code is designed as long it produce the result i wanted. — MG78, Dec 16 '16 at 09:37

score 0 · Accepted Answer · answered Dec 15 '16 at 05:47

0

Use this sub to do that.

    Sub CopyPaste()
        Range("A1").End(xlDown).Select
          Rows(Selection.Row).Select
           Selection.Copy
           Sheets("Sheet2").Activate
          Range("A1").Select
        ActiveSheet.Paste
    End Sub

answered Dec 15 '16 at 05:47

Harun24hr

30,391
4
21
36

Modify the sub as you need. This types of simple codes is available on internet. Do some google search, you will get tons of example. – Harun24hr Dec 15 '16 at 05:48
realy ? you advice using `Select` , `Selection` and `Activate` and `ActiveSheet` ? – Shai Rado Dec 15 '16 at 06:12
@ShaiRado I just shown him a simplest way to do that. He seems beginner of VBA. Better answer is appreciated. – Harun24hr Dec 15 '16 at 07:38

score 0 · Answer 2 · answered Dec 15 '16 at 06:14

As good practice, stay away from Select , Selection, Activate ,etc...

Instead, use referenced objects such as Sheets and Ranges. Also take the time to learn how to use With statements, it will help you a lot to create shorter and clearer codes, and reduces the chances for bugs.

Option Explicit

Sub CopyPaste()

Dim LastRow As Long

' copy last row from "Sheet1" >> modify to your sheet's name
With Sheets("Sheet1")
    ' find last row with data in column A , skip empty cells in the middle
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

    ' copy entire row with data from "Sheet1" to "Sheet2" first cell ("A1")
    .Rows(LastRow).Copy Destination:=Sheets("Sheet2").Range("A1")

End With

End Sub

score 0 · Answer 3 · answered Dec 15 '16 at 07:07

since you are interested in "values" and not to uselessly copy cells outside used range, you could do like follows:

Sub CopyPaste()
    With Sheets("SourceSheetName") '<--| reference "source" sheet (change "SourceSheetName" to your actual "source" sheet name)
        ' find last row with data in column A , skip empty cells in the middle
        With Range(.Cells(.Rows.count, "A").End(xlUp), _
                   .Cells(.Cells(.Rows.count, "A").End(xlUp).row, .Columns.count).End(xlToLeft)) '<--| reference its range from its column A last not empty cell to this latter cell row last not empty cell
            Worksheets("TargetSheetName").Range("A1").Resize(, .Columns.count).Value = .Value '<--| paste values to "target" sheet starting from its cell A1 (change "TargetSheetName" to your actual "target" sheet name)
        End With
    End With
End Sub

How do I copy the last nonempty row values to another sheet using VBA?

3 Answers3