I set up a worksheet and added a rectangle from the Drawing toolbar. I selected
it and then typed =a1 in the formula bar.

When I copied that sheet (edit|move or copy sheet), the rectangle on the new
sheet pointed to A1 of that new sheet (in the new workbook.

So I couldn't replicate your situation.

But this did work to change the formula to B1 of the new sheet:

Dim wks As Worksheet
Set wks = ActiveSheet

With wks
.Rectangles(1).Formula = "=B1"
End With

And this worked to remove the formula

Option Explicit
Sub testme01()
Dim wks As Worksheet
Set wks = ActiveSheet

wks.Copy 'to a new workbook

Set wks = ActiveSheet 'the new worksheet

With wks
.Rectangles(1).Formula = ""
End With

End Sub



Adrian T wrote:

Hello,

I created a rectangle on Sheet1 and named it txtClaim. It links to cell
Sheet1!A1 that has a value of "10-yr claim rate = 10%". The idea is that
everytime Sheet1!A1 is updated, the text in txtClaim is also updated
accordingly. Now, I want to make a copy of Sheet1 onto a new workbook. The
new txtClaim now links to [worksbook1.xls]Sheet1!A1. What I'd like to do is
to break the link and retain the text "10-yr claim rate = 10%", so the new
txtClaim does not depend on the original workbook anymore. How can I do it
programatically?

Please help,
Adrian T

--

Dave Peterson