Solved: Rewrite this line of code

[YellowLabPro]

In words, Select data starting at P4 down, copy, paste special.values to column O starting at 4 down.

This was recorded:
[vba]Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("O4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False[/vba]

This is what I desire to happen:
[vba]Range("P4:P").Selection.End(xlDown).Select
Selection.Copy
Range("O4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False[/vba]


thx,

ylp

Edited 9-Jun-06 by geekgirlau. Reason: insert vba tags

[Killian]

Can be done in one line if you refer directly to the ranges rather than selecting them first (as recorded) and use the "destination" argument of the Copy method[VBA]Range(Range("P4"), Range("P4").End(xlDown)).Copy Range("O4")[/VBA]

[YellowLabPro]

Killian,
Thanks. Follow-up.
The line of code did partially what I needed it to do. It needs to paste.special.values and the way it is written now is it only pastes. Can you edit?
Also:
I have read about selecting from the bottom up. Finding the last cell w/ data and copying up. This would help I think, my data changes frequently, and I am running into issues w/ either missing data or bombing my procedure.

ylp

[Killian]

Well if you have any gaps in the data, then xlDown may not be capturing the entire range you want. I might be better to use xlUp.
I've also defined the ranges here to make it a little clearer[VBA]Dim rngSource As Range
Dim rngtarget As Range

Set rngSource = Range("P4")
Set rngtarget = Range("O4")

Range(rngSource, Cells(Rows.Count, rngSource.Column).End(xlUp)).Copy rngtarget[/VBA]

[YellowLabPro]

Thanks,
I edited my reply to you, can you look at that?

[Killian]

Ahh yes, my bad... PasteSpecial
In that case, rather than using the target as an argument to copy (standard paste) it just need to be used in a new line as the subject of PasteSpecial[VBA]Set rngSource = Range("P4")
Set rngtarget = Range("O4")

Range(rngSource, Cells(Rows.Count, rngSource.Column).End(xlUp)).Copy
rngtarget.PasteSpecial xlPasteValues[/VBA]

[YellowLabPro]

Killian,
Thanks. This got me going in the right direction.

Have a great one.

YLP