Appending a formula in VBA

[Fyrea]

Hi All,

I have a formula I would like to append to in VBA.
I trying to essential automatically add some new tabs which I have managed to do, however in order for some of the statistics I pull from it, I need to add to a formula elsewhere in the workbook.

='Sheet1'!B19+'Sheet2'!B19
would become
='Sheet'!B19+'Sheet2'!B19+'SheetNew'!B19

As there are bout 20 formulas of this kind, I'd really like to avoid doing it manually.

 

[ScottInTexas]

Hi Fyrea. I'm not sure what you want. Where is the cell where the complete tally would go? That is what sheet, what cell is the answer? Are the cells you want data from always in B19 of the new sheet? The way I read this is you have a cell that totals, or averages or concatenates the values from B19 in Sheet1, Sheet2...SheetN. Clarify and I can help with the code.

 

[ScottInTexas]

Here is something to get you started. In the example below you would probably use the "Worksheet_SelectionChange" to get the range of the ActiveCell (where you want the formula).

Sub AppendFormula(ByVal rng As Range)
Dim wksht, sht As Worksheet
Dim str As String


    'rng is the range you pass when you call this. It could be
    'a call from Worksheet_Change like Target.Formula=AppendFormula(Target)
    Set wksht = Sheets("your worksheet")
    'This will build a string like Sheet1!B19 + Sheet2!B19
    For Each sht In Workbook.Sheets
        'This will exclude a certain sheet, like the one you are outting the formula in.
        If Not sht.CodeName = "some name" Then
            str = sht.CodeName & "!B19" & "+"
        End If
    Next
    'cut off the last + sign.
    str = Left(str, Len(str) - 1)
    wksht.Range(rng).Formula = "=" & str
    Set wksht = Nothing
End Sub

I haven't stepped through this, but it should work. Unless the set up is different.

 

[Fyrea]

My apologies, I was having an arguement with the forum earlier, it would only accept every 5th character I typed.

I can't give a full detail of what I am trying to do as not at my computer, however an example.
On sheet Front Page in cell b15, where formula is a sum of sheet1!b19 and sheet2!b19, add sheet3!b19 to sum range. It's always b19 but sheet3 would be a variable assigned by the tabnames set up earlier.

I do not wish to include all sheets in the formula, just specifically the named sheets.

In short;
Append "+sheet3!b19" to formula.'Front Page'!B15

If that makes sense....