Copy and paste from one row to another

[Patriot2879]

Hi good afternoon, please can you help me, i have the code below where i am trying to copy the whole row (R) and paste into row (F) it works but then i get an error pop up for the .entirerow part. Please can you help.

With Range("F2:F" & lr)
    .EntireRow.Copy Range("R2")
    .Rows(18).Copy Rows(6)
    .Value = .Value
End With

 

[Joe4]

Can you try explaining this again?
"F" and "R" are columns, not rows.

Maybe it would help to show us an example of what you want to do.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Patriot2879]

Hi this is my whole code below, i have data in column R, i want this copied to the last row. then i want this data copied into Column F. The code does work but i get a run time error 424 object required, and it highlights this line in yellow - .Copy = ("R2:R" & lr)

This is my whole code below, hope you can help?

Dim copySheet As Worksheet
Dim pasteSheet As Worksheet
Set copySheet = Worksheets("New")
Set pasteSheet = Worksheets("Combined")

Range("I2", Range("I2").End(xlDown)).Copy Range("B2")
Range("B2", Range("B2").End(xlDown)).NumberFormat = "0"
Range("J2", Range("J2").End(xlDown)).Copy Range("D2")
Range("R2", Range("R2").End(xlDown)).Copy Range("F2")
Dim lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
With Range("C2:C" & lr)
.Formula = "=VLOOKUP(B2,Old!B:C,2,FALSE)"
.Value = .Value
End With
With Range("E2:E" & lr)
.Formula = "=VLOOKUP(B2,Old!B:E,4,FALSE)"
.Value = .Value
End With
With Range("F2:F" & lr)
.Copy = ("R2:R" & lr)
.Value = .Value
End With
With Range("F2:F" & lr)
.EntireRow.Copy Range("R2")
.Rows(18).Copy Rows(6)
.Value = .Value
End With
With Range("F2:F" & lr)
.Formula = "=VLOOKUP(LEFT(F2,LEN(F2)-2),Postcodes!A:B,2,FALSE)"
.Value = .Value

With copySheet
.Range(.Cells(2, "A"), .Cells(.Cells(Rows.Count, "A").End(xlUp).Row, "AD")).Copy
End With

pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Sheets("Combined").Select
ActiveSheet.Range("B2").EntireColumn.Select
Selection.NumberFormat = "0"
ActiveSheet.Range("L2").EntireColumn.Select
Selection.NumberFormat = "dd/mm/yyyy"
ActiveSheet.Range("AD2").EntireColumn.Select
Selection.NumberFormat = "dd/mm/yyyy"
End With
End Sub[/CODE]

 

[Joe4]

.Copy = ("R2:R" & lr)

is not a valid range reference.

Try:

.Copy = Range("R2:R" & lr)