Or better yet work from the bottom up...
Range("P2").Copy
Range("P3",Range("P" & Rows.Count).End(xlUp)).PasteSpecial xlPasteAll

--
Charles Chickering

"A good example is twice the value of good advice."


"Astello" wrote:

I just realized that there were some empty cells, so the
ctrl+shift+down only selected up to the empty cell and didn't paste the
cells below it. i'll have to write a function to get rid of rows with
empty cells, or fill empty cells with a placeholder.

Charles Chickering wrote:
The code you have should work just fine. However there is a shorter way to do
it.
Range("P2").Copy
Range("P3",Range("P3").End(xlDown)).PasteSpecial xlPasteAll

The Ctrl+Down thing is basically the "End(xlDown)" part.
--
Charles Chickering

"A good example is twice the value of good advice."


"Astello" wrote:

I'm using a macro to basically add new columns to data pulled from
Access. I pulled 4 extra columns that I can re-write to be the same
length as the data I actually need. So in my macro, I recorded
Ctrl+Shift+Down to paste the data the length of the column, but this
function does not get translated when I pull data that is longer than
the original data I wrote the macro on. Is there a way to re-write
this so that it goes to the end of the column each time it is executed,
no matter how long the column is?

Here is the code I have:

Range("P2").Select
Selection.Copy
Range("P3").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-3