help with .Cells()

[fayez_MrExcel]

Is there any way to write the syntax below in one line only? Thanks

.Cells(sRow, "A").Copy
Cells(dRow, "E").PasteSpecial xlValues
.Cells(sRow, "B").Copy
Cells(dRow, "F").PasteSpecial xlValues
.Cells(sRow, "Y").Copy
Cells(dRow, "G").PasteSpecial xlValues
.Cells(sRow, "AB").Copy
Cells(dRow, "H").PasteSpecial xlValues
Cells(dRow, "D").Value = serial_number

 

[Lewiy]

Give this a try:

.Cells(sRow, "A").Copy Destination:= Cells(dRow, "E") 
.Cells(sRow, "B").Copy Destination:= Cells(dRow, "F")
.Cells(sRow, "Y").Copy Destination:= Cells(dRow, "G")
.Cells(sRow, "AB").Copy Destination:= Cells(dRow, "H") 
Cells(dRow, "D").Value = serial_number

 

[Scott Huish]

I don't know about 1 line, but I got it in 3:

Union(.Cells(srow, "A"), .Cells(srow, "B"), .Cells(srow, "Y"), .Cells(srow, "AB")).Copy
Range(Cells(drow, "E"), Cells(drow, "H")).PasteSpecial xlPasteValues
Cells(drow, "D") = serial_number

 

[Lewiy]

Ok, cheating a little bit but here’s it in 1!

Union(.Cells(srow, "A"), .Cells(srow, "B"), .Cells(srow, "Y"), .Cells(srow, "AB")).Copy Destination:=Range(Cells(drow, "E"), Cells(drow, "H")) : Cells(drow, "D") = serial_number

 

[Scott Huish]

Ok, cheating a little

I was really trying not to go there

Although I gotta say that is interesting, I wouldn't have expected that to paste values. As long as the source range is contiguous, it pastes formulas but when the source range is uncontiguous it pastes values.

 

[Lewiy]

Although I gotta say that is interesting, I wouldn't have expected that to paste values

Me neither, but I discovered this a few weeks ago. It's quite a handy shortcut against PasteSpecial xlPasteValues. I guess you could also use Range(Range1).Value = Range(Range2).Value.

 

[fayez_MrExcel]

Thanks guys