Copy last row and paste into specified range

Question

I am trying to copy the last row of entered data from the range B7:O34 and paste it into the range A7:O7.

When I run this macro I would like it to overwrite the data in A7:O7 and not continue listing the data in consecutive rows.

This is what I have tried:

Sub CopyLastRow()
    Dim lRw As Long
    With ActiveSheet
        lRw = Cells.Find("", .Range("A" & Rows.Count), , , xlByRows, xlPrevious).Row
        .Rows(lRw).Copy
    End With 
    With Sheets("Sheet1")
        lRw = Cells.Find("", .Range("A" & Rows.Count), , , xlByRows, xlPrevious).Row
        .Rows(lRw + 1).PasteSpecial Paste:=xlPasteValues
    End With
End Sub

I have tried using the below Sub CopyLastRow() Dim lRw As Long With ActiveSheet lRw = Cells.Find("", .Range("A" & Rows.Count), , , xlByRows, xlPrevious).Row .Rows(lRw).Copy End With With Sheets("Sheet1") lRw = Cells.Find("", .Range("A" & Rows.Count), , , xlByRows, xlPrevious).Row .Rows(lRw + 1).PasteSpecial Paste:=xlPasteValues End With End Sub But this copies information outside of the desired range of B7:O34 and is effectively the footer for my sheet — dgray21781
– dgray21781, Commented Aug 29, 2015 at 12:26
..Lacking details.. put the code in your question so it is readable. — Davesexcel
– Davesexcel, Commented Aug 29, 2015 at 12:32
I am trying to paste the code in but it comes up all screwed up. I wish to copy a horizontal row range and paste in a horizontal range. The last row of data in the range B7:O34. Each row B7:O7, B8:O8 etc all contain info but not necessarily all the way to row 34. I need to copy the last row with data and paste it into B6:O6 — dgray21781
– dgray21781, Commented Aug 29, 2015 at 12:50

paul bica · Accepted Answer · 2015-08-29 13:18:58Z

1

Option Explicit

Sub CopyLastRow()

    Dim lR As Long, lC As Long, r1 As Range, r2 As Range

    With ActiveSheet
        lR = .Cells(.Rows.Count, 2).End(xlUp).Row           'last row - should be 34
        lC = .Cells(7, .Columns.Count).End(xlToLeft).Column 'last col - should be "O" (15)

        Set r1 = .Range(.Cells(lR, 2), .Cells(lR, lC))      'last row of data
    End With

    With Sheets("Sheet1")
        Set r2 = .Range(.Cells(6, 2), .Cells(6, lC))        'row 6 on Sheet1
    End With

    r2.Value2 = r1.Value2

End Sub

answered Aug 29, 2015 at 13:18

paul bica

10.8k4 gold badges27 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Davesexcel · Accepted Answer · 2015-08-29 13:06:20Z

0

Here you go..

Sub Button1_Click()
    Dim sh As Worksheet, ws As Worksheet
    Dim Rws As Long, rng As Range, Prng As Range

    Set sh = ActiveSheet
    Set ws = Sheets("Sheet1")
    Set Prng = ws.Range("B6")

    With sh
        Rws = .Range("B34").End(xlUp).Row
        Set rng = .Range("B" & Rws & ":O" & Rws)
    End With

    rng.Copy Prng

End Sub

answered Aug 29, 2015 at 13:06

Davesexcel

6,9922 gold badges31 silver badges44 bronze badges

Collectives™ on Stack Overflow

Copy last row and paste into specified range

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related