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:
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("O4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

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


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 methodRange(Range("P4"), Range("P4").End(xlDown)).Copy Range("O4")

[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 clearerDim 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

[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 PasteSpecialSet rngSource = Range("P4")
Set rngtarget = Range("O4")

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

[YellowLabPro]

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

Have a great one.

YLP