Copy Worksheet Formula Problem

[Guest]

I am coping a worksheet (in the same workbook) and then renaming the sheet
all in vba code. There are formulas on the source sheet, and copied sheet,
that point to a third sheet. The problem is that my formulas do not update
when I change data on a third sheet.

I tried coping the sheet then reentering the formula, in VBA, but that does
not work either.

Note that application calculation is turn to automatic

Any thoughts?

Thanks in advance,

Stewart

 

[Guest]

Do you turn auto-calculation off prior to copying the worksheet? Then back on
again? Do the formulas work on the sheet you copy before the VBA is executed?
How about after? Are the formulas longer than 127 characters? Is the sheet
you are copying protected when you copy it? Have you tried "F9"?

Dale Preuss

 

[Guest]

First off thanks for your reply
Secondly ... here are your questions answered


Reply of not an issues:

autocalc is always set to true ... never turned off or on
Formulas work before the copy
formulas are less that 127 characters
Trying F9 does not resolve issue

Reply WITH issue:

The source copy sheet is protected when copied .. specifically the cells
that the formulas are coming from. Protection is then turned off and the
formulas are being replaced ... ie ... cells(10,10).formula = "='Sheet1'!a10.
Then cell protection is turned on.

 

[Guest]

Try turning the protection off of the source worksheet before copying it.
When a worksheet is protected, the formulas don't travel, just the values.

Dale