excel macro copies last row of data

[eng819]

I need an excel macro that will copy the last row of data from one worksheet within a workbook to another worksheet in the same work book. The last row number of data will alwasy vary

 

[VoG]

Try like this

Sub cpy()
Dim LR As Long
With Sheets("Sheet1")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    .Rows(LR).Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
End With
End Sub

 

[eng819]

That worked!! But, I forgot to mention that it's a "copy special" and I need to copy "values"

 

[JoeMo]

That worked!! But, I forgot to mention that it's a "copy special" and I need to copy "values"

Then this:

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("Sheet2")
    lRw = Cells.Find("*", .Range("A" & Rows.Count), , , xlByRows, xlPrevious).Row
    .Rows(lRw + 1).PasteSpecial Paste:=xlPasteValues
End With
End Sub

 

[eng819]

used this code first and it copied last row of data to top row of another worksheet, but I need it to "paste special" and only past values.

Sub copy()
Dim LR As Long
With Sheets("1st Shift DTR MF")
LR = .Range("A" & Rows.Count).End(xlUp).Row
.Rows(LR).copy Destination:=Sheets("All DTR Mean Time MF").Range("A" & Rows.Count).End(xlUp).Offset(1)
End With
End Sub

used this code and it "paste special" the values correctly into the correct worksheet, but copied into the next row down from original row, in original worksheet. Original data in ramained selected also.

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("All DTR Mean Time MF")
lRw = Cells.Find("*", .Range("A" & Rows.Count), , , xlByRows, xlPrevious).Row
.Rows(lRw + 1).PasteSpecial Paste:=xlPasteValues
End With
End Sub

I've tried some combinations of the two but still get errors.

 

[VoG]

You are missing a dot

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("All DTR Mean Time MF")
lRw = .Cells.Find("*", .Range("A" & Rows.Count), , , xlByRows, xlPrevious).Row
.Rows(lRw + 1).PasteSpecial Paste:=xlPasteValues
End With
End Sub

 

[eng819]

sorry this is so confusing.

VoG - first post you sent copied last row of data from one worksheet to the top row of another worksheet. That part was correct. But I needed it to "paste special" the values instead of copy.

 

[eng819]

Your first code was correct except for the "paste Special" values

Sub copy()
Dim LR As Long
With Sheets("1st Shift DTR MF")
LR = .Range("A" & Rows.Count).End(xlUp).Row
.Rows(LR).copy Destination:=Sheets("All DTR Mean Time MF").Range("A" & Rows.Count).End(xlUp).Offset(1)
End With


I tried using this substituting " PasteSpecial Paste:=xlPasteValues " for "copy" in the 5th row of the code and received an error

Sub copy()
Dim LR As Long
With Sheets("1st Shift DTR MF")
LR = .Range("A" & Rows.Count).End(xlUp).Row
.Rows(LR).PasteSpecial Paste:=xlPasteValues Destination:=Sheets("All DTR Mean Time MF").Range("A" & Rows.Count).End(xlUp).Offset(1)
End With

 

[VoG]

try

Sub cpy()
Dim LR As Long
With Sheets("Sheet1")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    .Rows(LR).Copy
    Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
End With
End Sub

 

[HalfAce]

If you're pasting special (Values) then why copy at all? You can simply make the values equal. (ie. - using Vog's code...)

Sub cpy()
Dim LR As Long
With Sheets("Sheet1")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp)(2).EntireRow.Value = _
    .Rows(LR).EntireRow.Value
End With
End Sub

Hope it helps.