What are the reasons for ""ActiveSheet.Paste Link:=True"" failure

[CAPTGNVR]

DEAR ALL
What I need is for example to copy contents in say b4.c4 to e4.f4. On
copying I want e4.f4 as a link so that any changes in b4.c4 would
relfect automatically in e4.f4.
So I wrote in VBA and it works fine if I do step by step using F8.
But when run thro the command button the ActiveSheet.Paste Link:=True
fails. Mr. Tom adviced to set the focus to false and did not work.
Pls advice how to achieve this. I will check for response every
30mints. Pls help.

 

[Bob Phillips]

This works fine for me

Range("B4:C4").Copy
Range("E4:F4").Select
ActiveSheet.Paste link:=True


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[CAPTGNVR]

This works fine for me

Range("B4:C4").Copy
Range("E4:F4").Select
ActiveSheet.Paste link:=True

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

D/BOB
thnks. my code is asf:

Activecell.select
Selection.Range(Cells(1, 46), Cells(1, 47)).Select
Selection.Copy
F_CELL.Select
Selection.Offset(0, 4).Select
ActiveSheet.Paste Link:=True ' at this place i get the error that
paste method class failed

but when i select debug from the error and press F8 it does copy. Pls
explain. Or is anyother way to copy from one cell to another cell as
a link thro vba??. whole day i will be checking as the whole program
has come to a halt bcos of this failure to copy as a link --
activesheet.pastelink failure.

 

[Bob Phillips]

I have no idea what F_CELL is, but without it, this worked fine for me, from
a button

Selection.Range(Cells(1, 46), Cells(1, 47)).Copy
Selection.Offset(0, 4).Select
ActiveSheet.Paste Link:=True


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[CAPTGNVR]

I have no idea what F_CELL is, but without it, this worked fine for me, from
a button

Selection.Range(Cells(1, 46), Cells(1, 47)).Copy
Selection.Offset(0, 4).Select
ActiveSheet.Paste Link:=True

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

D/BOB
So eagerly i am waiting to solve this. F_CELL is the one I set it as
say A1. So what my vba does is to take this value and goto another
range and locate that. Once located it is set as F_CELL2.

So i used the F-Cell to go back to A1 and copy the two adjacent cells
and go to the new location set as F_CELL2 and paste the link. i am
using excel 97. Unable to figure out why it does not work when use
the command button but when i am testing it in the visual basic editor
by using F8 it works just fine. Some how dont give up Bob. Help me

 

[CAPTGNVR]

I have no idea what F_CELL is, but without it, this worked fine for me, from
a button

Selection.Range(Cells(1, 46), Cells(1, 47)).Copy
Selection.Offset(0, 4).Select
ActiveSheet.Paste Link:=True

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

D/BOB further info to make it more ustanding:

Activecell.select (this is the place where i found the match and set
it as F_CELL2)

Selection.Range(Cells(1, 46), Cells(1, 47)).Select (I WANT TO COPY
THIS RANGE)

Selection.Copy
F_CELL.Select (THIS IS THE FIRST PLACE WHERE I STARTED FROM AND EASY
TO REFERENCE FROM HERE OTHER WISE I HAVE TO STRUGGLE TOO MUCH TO TRACE
BACK USING THE OFFSET FROM THE F_CELL2.)

Selection.Offset(0, 4).Select ( WORKS FINE AND GETS SELECTED)

ActiveSheet.Paste Link:=True ( ERROR PROPS UP AT THIS PLACE SAYING
THAT PASTING METHOD CLASS FAILED--)

 

[Bob Phillips]

Can you post me the workbook?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[CAPTGNVR]

Can you post me the workbook?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

D/BOB
I dont know how to post the sheet in this group. So i hv sent you the
email.

 

[Tim Williams]

Did you try my suggestion at your previous post (this appears to be the same
problem).
If you're using a commandbutton to call this code then make sure its
"takefocusonclick" property is set to False.

Tim

 

[Guest]

suspect your "Mr Tom":

Mr. Tom adviced to set the focus to false and did not work.

from the OP's original post in this thead.