Copy/Paste_Value problem.

[Cimjet]

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

 

[Fan924]

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

 

[joeu2004]

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")

 

[Cimjet]

Hi Joeu2004
Thank you very much, it works perfectly.
Cimjet

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")

 

[Dave Peterson]

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.)

 

[Cimjet]

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