PasteSpecial into first empty cell in a column

[alanos206]

I'm trying to pastespecial into the first empty cell in a column.

I've used the below in another macro that just pastes the value into a cell and works fine. What is required to do this in a column? Thanks for any assistance.

Sub Copy()
Sheets("Pre_2001").Range("H8").Copy
With Sheets("Quantification").Range("R2").End(xlToLeft).Offset(, 17)
.PasteSpecial xlPasteFormats
.PasteSpecial xlPasteValues
End With
End Sub

[alanos206]

And I'm copying from another worksheet if it isn't clear from the code. thanks

[Doc.AElstein]

Hi alanos206
Please use code tags (

Please Login or Register  to view this content.

[/noparse] ) when Posting code: Rule 3: https://www.excelforum.com/forum-rul...rum-rules.html

You should see this for your code just before you post in the Forum editor
[code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code]
_.______________
I am not totally clear on what it is you want to do
Possibly what you need to do is determine the (last row +1) in a column, see here:
https://www.excelforum.com/hello-int...ml#post4815736


This would put something in the next free row in column A in Worksheet "Quantification"

Please Login or Register  to view this content.

Alan

[alanos206]

Thank you. The only issue is that the column cells that I'm pasting to already have data in the column cells preceding them. So while the code works its pasting into the first cell in column "R" where there is no data. Data needs to be pasted in the highlighted cell. Thanks so much! Alan

Capture.JPG

[xlnitwit]

You can specify the column to find the last row in

Please Login or Register  to view this content.

[Doc.AElstein]

Hi alanos206
The code is set to find the last used row in column 1 ( Clm = 1 ) ( Column “A” )
I assume you already changed the column for the PasteSpecial to R ? , otherwise my code would be pasting into column A
So you just need to adjust Clm to that for column R
So Clm = 18

Alan

Edit: Or like Don said

( Belated Happy New Year , Don :-) )

[xlnitwit]

Happy New Year to you too, Alan!

I find it a lot simpler to use column letters rather than numbers.

[Doc.AElstein]

Hi Don,
I am not prejudice, but confess to having a tenancy towards the column letter also!
I usually have a simple Function kicking around to convert any column numbers into letters.
I don’t like the Cell(r, c) type thingy much… I will usually convert a column number to a letter and use the Range(“ “) variation. - I feel a lot more comfortable with that
https://www.excelforum.com/excel-pro...ml#post4813760
Alan

[alanos206]

Works great! Really appreciate the assistance. Alan

[Doc.AElstein]

Yous welcome, thanks for the feedback.
Don made a good point about the column letter possibillity, which I too prefer actually
Alan

Edit: Please mark the Thread As Solved, if I was quick enough to catch you, Thanks
( ...Most of the excelforum software can be a bit quirky sometimes… you might be able to figure out how to do it from this: https://www.excelforum.com/developme...ml#post4668790 …. If you are lucky the first simple Method might work…
Mark Solved Dali sandy.jpg https://imgur.com/bfV11kW )

[xlnitwit]

I usually have a simple Function kicking around to convert any column numbers into letters.

Seems like a lot of effort for no real benefit to me.

[Doc.AElstein]

No one understands the ‘_- prettyness of my codes '_- Let me be, .. me

Please Login or Register  to view this content.

[xlnitwit]

Variety is the spice of life, as they say.

As long as I don't have to deal with it, do whatever makes you happy...