Copy/Paste_Value problem.
 

Hi Everyone
I'm using this vba line to retrieve Values from one sheet but when the values
are formulas, they retrieve the formula instead of the value. I need to convert
it to value and I can't seem to use Paste Special.

Lastrow = Sheets("Summary").Cells(Cells.Rows.Count, "A").End(xlUp).Row
Sheets("Invoice").Range("B2").Copy _
Destination:=Sheets("Summary").Range("A" & Lastrow + 1)
Thank you
Cimjet

Copy/Paste_Value problem.
 

Sheets("Sheet1").Select
Range("C1").Select
Selection.Copy
Sheets("Sheet2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

Copy/Paste_Value problem.
 

On Apr 1, 6:42*am, "Cimjet" wrote:
I'm using this vba line to retrieve Values from one sheet
but when the values are formulas, they retrieve the formula
instead of the value. I need to convert it to value and I
can't seem to use Paste Special.
[....]
Sheets("Invoice").Range("B2").Copy _
* *Destination:=Sheets("Summary").Range("A" & Lastrow + 1)

Well, you could do the following:

Sheets("Invoice").Range("B2").Copy
Sheets("Summary").Range("A" & Lastrow + 1).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats

But there is no need to involve the clipboard. Simply do the
following:

Sheets("Summary").Range("A" & Lastrow + 1) = _
Sheets("Invoice").Range("B2")

Copy/Paste_Value problem.
 

Hi Joeu2004
Thank you very much, it works perfectly.
Cimjet
"joeu2004" wrote in message
...
On Apr 1, 6:42 am, "Cimjet" wrote:
I'm using this vba line to retrieve Values from one sheet
but when the values are formulas, they retrieve the formula
instead of the value. I need to convert it to value and I
can't seem to use Paste Special.
[....]
Sheets("Invoice").Range("B2").Copy _
Destination:=Sheets("Summary").Range("A" & Lastrow + 1)

Well, you could do the following:

Sheets("Invoice").Range("B2").Copy
Sheets("Summary").Range("A" & Lastrow + 1).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats

But there is no need to involve the clipboard. Simply do the
following:

Sheets("Summary").Range("A" & Lastrow + 1) = _
Sheets("Invoice").Range("B2")

Copy/Paste_Value problem.
 

Why can't you use pastespecial values?

workSheets("invoice").range("b2").copy
worksheets("summary").range("A" & lastrow + 1).pastespecial _
paste:=xlpastevalues

Or just assign the value:
worksheets("summary").range("A" & lastrow + 1).value2 _
= workSheets("invoice").range("b2").value2

(.value2 is nice for dates and currency. And it doesn't hurt with other types.)



On 04/01/2011 08:42, Cimjet wrote:
Hi Everyone
I'm using this vba line to retrieve Values from one sheet but when the values
are formulas, they retrieve the formula instead of the value. I need to convert
it to value and I can't seem to use Paste Special.

Lastrow = Sheets("Summary").Cells(Cells.Rows.Count, "A").End(xlUp).Row
Sheets("Invoice").Range("B2").Copy _
Destination:=Sheets("Summary").Range("A" & Lastrow + 1)
Thank you
Cimjet

--
Dave Peterson

Copy/Paste_Value problem.
 

Hi Dave
I like your last suggestion "Value2" I'll keep it on file.
I'm using Joeu2004 last suggestion and it works great.
To answer your question Why can't you use pastespecial values?
Simply, I'm new to VBA, no experience and little practice, that's why I call on
you Guys help.
It's very much appreciated.
Thanks for your post.
Cimjet

"Dave Peterson" wrote in message
...
Why can't you use pastespecial values?

workSheets("invoice").range("b2").copy
worksheets("summary").range("A" & lastrow + 1).pastespecial _
paste:=xlpastevalues

Or just assign the value:
worksheets("summary").range("A" & lastrow + 1).value2 _
= workSheets("invoice").range("b2").value2

(.value2 is nice for dates and currency. And it doesn't hurt with other
types.)



On 04/01/2011 08:42, Cimjet wrote:
Hi Everyone
I'm using this vba line to retrieve Values from one sheet but when the values
are formulas, they retrieve the formula instead of the value. I need to
convert
it to value and I can't seem to use Paste Special.

Lastrow = Sheets("Summary").Cells(Cells.Rows.Count, "A").End(xlUp).Row
Sheets("Invoice").Range("B2").Copy _
Destination:=Sheets("Summary").Range("A" & Lastrow + 1)
Thank you
Cimjet

--
Dave Peterson