Paste Special in VBA

OZDOC1050

unread,

Nov 29, 2002, 4:44:53 AM11/29/02

to

Hi Simon,
Before You paste you must copy. (PS Try not to post files
just an explanation usually does the trick)
HTH
R
Pete

Range("A4").Select
Selection.Copy
Range("C8").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Application.CutCopyMode = False

"Simon Freedman" <simon.f...@energis.com> wrote in message
news:1e73c01c2978b$7dc2c540$8af82ecf@TK2MSFTNGXA03...
> I am trying to write a macro to Paste Special, Value in
> Excel.
>
> The macro I have written (called 'valuev in the attached
> file) uses the following:
>
> Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
> SkipBlanks:= _ False, Transpose:=False
>
> but I am unable to paste special, values - can anyone help?
>
> many thanks
>

Simon Freedman

unread,

Nov 29, 2002, 5:32:05 AM11/29/02

to

Thanks for your quick reply. What you say is correct, however what I
need to be able to do is to manually copy, then Paste Speacial, Value
wherever I need to, rather than having the Copy, cell selection and
Paste Special, Value functions all in the macro

When I Ctrl + C, then run my original macro, it comes up with an 1004
internal error message

any ideas?

thanks

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Martin Gerhold

unread,

Nov 29, 2002, 6:17:27 AM11/29/02

to

"Simon Freedman" <simon.f...@energis.com> wrote in message

news:e8V1QK5lCHA.2840@tkmsftngp04...

> Thanks for your quick reply. What you say is correct, however what I
> need to be able to do is to manually copy, then Paste Speacial, Value
> wherever I need to, rather than having the Copy, cell selection and
> Paste Special, Value functions all in the macro
>
> When I Ctrl + C, then run my original macro, it comes up with an 1004
> internal error message
>
> any ideas?

Well, it works fine for me - I tried running the macro with a hot key, and
also from within the VBA editor (having copied something to the paste
buffer). So I tried 'tools>macro>macros>run' and got the 1004 error. I
think it is because the range you are pasting to (the currently selected
cell) is somehow 'lost' by going through the menu structure.

HTH Martin

Simon Freedman

unread,

Nov 29, 2002, 6:30:43 AM11/29/02

to

well I wante dto use a toolbar button for it anyway, so that works just
fine!

thanks for your help

Tommy Long

unread,

Nov 29, 2002, 8:21:54 AM11/29/02

to

Hmmm... in addition to this post, on occaision my user
wants to paste to excel, values from a GQL / SQL query.

Alhtough I can instruct them to copy, I tried to use the
code mentioned to paste values but it never works! (And it
is definately copied first).

The only way to do this, is when the values are copied
from an excel sheet.

Is this an issue or am I doing something wrong?

Regards.
Tommy Long
Business Services Team
Barclays House, Dorset

>.
>

JMMach

unread,

Nov 29, 2002, 8:49:28 AM11/29/02

to

A simpler way:
'Suppose you want to paste special what is in cell B5, use the statement:
Cells(5,2) = Cells(5,2) ' interpreted as the NEW value of Cells(5,2) = the
OLD value of Cells(5,2)
JMMach

--
TTFN
JMMach