Thread: How can I paste value from a variable with Destination:=

You mean?

OutSh.Cells(rows.Count, 1).End(xlUp).Offset(1, 1).Value = Temp2

Without know the value of Temp2, it is hard to know what you mean. Copying the Value and pasting are two different things.

Originally Posted by VBA Noob

Maybe

Destination:=OutSh.Cells(Temp2,1)

So Assume Temp2 = 10 it would paste to Cell A10

VBA Noob

Thank you for your replay VBA Noob
But If I want to include this part where that counts the rows to the last used row, Cells(rows.Count, 1).End(xlUp)

Is that possible?

Originally Posted by Kenneth Hobson

You mean?

OutSh.Cells(rows.Count, 1).End(xlUp).Offset(1, 1).Value = Temp2

Without know the value of Temp2, it is hard to know what you mean. Copying the Value and pasting are two different things.

I only want to paste the value in Temp2, not copy values to it. And the variable Temp2 is a "long", containing numbers.

I'm not sure I explained so you guys understood what I wanted with the code.

It is like this.
I have created a variable (long) called Temp2.
Temp2 is used to store numbers created by a nother procedure. That is working fine and is nothing I need help with here. What I need help with is how can I put this number that is stored in the Temp2 variable into the sheet (OutSh). Can it be done using the same method as described above with the (Destination:=) method? It would be great if it could check for the last row, just like the code above does and put it there and use the offset to select column, like the code below (although that code use range as input instead of Temp2)

Range("B2").Copy Destination:=OutSh.Cells(rows.Count, 1).End(xlUp).Offset(1, 1)

Thanks for you help, I understand now that I did not explain well enough before what I wanted the code to do.

You first said that Temp2 was a string. Now, you say it is a long. In anycase, the solution is the same.

I gave you the solution providing that your code on the left is what you wanted.

OutSh.Cells(rows.Count, 1).End(xlUp).Offset(1, 1).Value = Temp2

Had you wanted to put a cell value into temp2 then swap the values:

Temp2 = OutSh.Cells(rows.Count, 1).End(xlUp).Offset(1, 1).Value

You can use this to test where your code on the left is pointing to by:

MsgBox OutSh.Cells(rows.Count, 1).End(xlUp).Offset(1, 1).Address