PDA

View Full Version : Solved: UPDATE FORMULA



alan23jm
04-02-2008, 12:18 AM
Hi All,

I have a workbook with a main sheet with formulas and the values are dependent on the sheets I am adding. I want the sheet with formula to update after I add the sheet where it is dependent. What I actually do now is to double click the cells with formula one by one to get the result. Is there a code that I can place on the main sheet so that after I add a sheet, i will just hit a button to update?

Bob Phillips
04-02-2008, 12:59 AM
This should happen automatically already.

What problem do you have exeactly?

georgiboy
04-02-2008, 01:41 AM
You could try putting this code to a button in your main sheet with formula, change the sheet name/column names to suit your sheet i.e where you formulas are. Add more columns also.

Sub UpdateFormula()

Sheets("Sheet1").Columns("A:A") _
.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 1), TrailingMinusNumbers:=True

Sheets("Sheet1").Columns("C:C") _
.TextToColumns Destination:=Range("C1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 1), TrailingMinusNumbers:=True

End Sub

hope this helps

alan23jm
04-02-2008, 03:19 AM
i attached a sample file


if you will notice from the example there is a sheet 4 added which should have updated the "stat" sheet. what i need is to place a button on "stat" sheet with macro that everytime i add a new sheet, the formulas will update and give me the result once i hit the button.

georgiboy
04-02-2008, 04:01 AM
Is this what you mean, i have attatched.

alan23jm
04-02-2008, 04:36 AM
thanks a lot georgiboy, this is what i needed....i just added the same code for column G