Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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") |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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") |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
V-Look up copy problem-PLEASE HELP | Excel Worksheet Functions | |||
A visual basic value copy BUG?? - accounting format has copy problem!! | Excel Programming | |||
Copy Problem | Excel Programming | |||
Copy Problem | Excel Discussion (Misc queries) | |||
copy problem | Excel Programming |