Coping Cell Values

boydr · Apr 20th, 2002, 07:39 AM

I'm using the following in a macro script to copy the value of one cell to a new worksheet. Does anyone know how I can copy the value of the cell so that when the source cell has a formula it won't copy the formula, but will copy the value only?

Sheets(CurrSheet).Range("A4").Copy Destination:=Sheets(NewSheet).Range("A4")

paelinck · Apr 20th, 2002, 07:56 AM

Hi boydr,

try this

Code:

Sheets(CurrSheet).Range("A4").Copy 
Sheets(NewSheet).Range("A4").PasteSpecial Paste:=xlValues

Marc

Tom Schreiner · Apr 20th, 2002, 12:14 PM

Also...

Sheets("NewSheet").Range("A4").value = _
Sheets("CurrSheet").Range("A4").value

Tom

boydr · Apr 20th, 2002, 02:34 PM

Thanks for the help, but I can't get it to work. I know I'm leaving something out, but not sure what. This is what I tried:

Sheets("NewSheet").Range("I4").Value Destination:=Sheets("CurrSheet").Range("B2").Value

barryt · Apr 20th, 2002, 02:49 PM

Record a macro
and select the cell
then copy
Then select the cell that you want to paste
then paste special and have the options that paste just the value

which this will do the same as paelinck code
whihc should work

boydr · Apr 20th, 2002, 04:50 PM

Tried that too, but it didn't work. I pasted in this:

Sheets(CurrSheet).Range("I6").Select
Selection.Copy
Sheets(NewSheet).Range("B6").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

But I get the error: "Select method of Range class failed" message.

[ This Message was edited by: boydr on 2002-04-20 15:54 ]

boydr · Apr 21st, 2002, 08:33 AM

Actually, Tom, you're suggestion worked after I tried it again today. Not sure what I was doing yesterday, but thanks for your help and everyone elses.

Apr 20th, 2002, 07:56 AM	#2
paelinck Board Regular Join Date: Mar 2002 Location: Leiden, The Netherlands Posts: 68	Hi boydr, try this Code: Sheets(CurrSheet).Range("A4").Copy Sheets(NewSheet).Range("A4").PasteSpecial Paste:=xlValues Marc

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 20th, 2002, 07:39 AM	#1
boydr Board Regular Join Date: Apr 2002 Location: Arkansas Posts: 157	I'm using the following in a macro script to copy the value of one cell to a new worksheet. Does anyone know how I can copy the value of the cell so that when the source cell has a formula it won't copy the formula, but will copy the value only? Sheets(CurrSheet).Range("A4").Copy Destination:=Sheets(NewSheet).Range("A4")

Apr 20th, 2002, 12:14 PM	#3
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Also... Sheets("NewSheet").Range("A4").value = _ Sheets("CurrSheet").Range("A4").value Tom

Apr 20th, 2002, 02:34 PM	#4
boydr Board Regular Join Date: Apr 2002 Location: Arkansas Posts: 157	Thanks for the help, but I can't get it to work. I know I'm leaving something out, but not sure what. This is what I tried: Sheets("NewSheet").Range("I4").Value Destination:=Sheets("CurrSheet").Range("B2").Value

Apr 20th, 2002, 02:49 PM	#5
barryt New Member Join Date: Apr 2002 Posts: 15	Record a macro and select the cell then copy Then select the cell that you want to paste then paste special and have the options that paste just the value which this will do the same as paelinck code whihc should work

Apr 20th, 2002, 04:50 PM	#6
boydr Board Regular Join Date: Apr 2002 Location: Arkansas Posts: 157	Tried that too, but it didn't work. I pasted in this: Sheets(CurrSheet).Range("I6").Select Selection.Copy Sheets(NewSheet).Range("B6").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False But I get the error: "Select method of Range class failed" message. [ This Message was edited by: boydr on 2002-04-20 15:54 ]

Apr 21st, 2002, 08:33 AM	#7
boydr Board Regular Join Date: Apr 2002 Location: Arkansas Posts: 157	Actually, Tom, you're suggestion worked after I tried it again today. Not sure what I was doing yesterday, but thanks for your help and everyone elses.