Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Worksheet Formula Problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Worksheet Formula Problem
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 "Datasort" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Worksheet Formula Problem
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. "Dale Preuss" wrote: 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 "Datasort" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Worksheet Formula Problem
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 "Datasort" wrote: 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. "Dale Preuss" wrote: 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 "Datasort" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 Copy Worksheet Problem | Excel Discussion (Misc queries) | |||
Worksheet copy problem - local names | Excel Discussion (Misc queries) | |||
Worksheet copy problem | Excel Programming | |||
worksheet copy problem | Excel Programming | |||
worksheet copy problem | Excel Programming |