0

New to these forums (posting anyway - been using them as a reference for a while!).

I have a formula that calculates average unit size by adding up cells from other sheets in the same book.

I have a macro that creates a new template sheet, and am now working on the macro to update the summary tab with the data from the newly created sheet.

For Example my formula reads:

=SUM(Sheet2!X7,Sheet3!X7,Sheet4!X7)/F8

F8 is the total number of units, each X7 is the total unit size.

What I can't work out is if a macro can edit the above formula so that when I have added sheet 5, I run the macro and it edits the formuala to read:

=SUM(Sheet2!X7,Sheet3!X7,Sheet4!X7,Sheet5!X7)/F8
YowE3K
  • 23,852
  • 7
  • 26
  • 40
adbc83
  • 3
  • 1
  • Are all your formulae of the same type or a mixture? – SJR Oct 12 '17 at 16:34
  • Use Tim's function as seen [here](https://stackoverflow.com/questions/6688131/test-or-check-if-sheet-exists) then change the formula value if it returns true. – dwirony Oct 12 '17 at 16:35
  • Does it need to edit an existing formula or can it set a cell or cell range .FormulaR1C1 ? – QHarr Oct 12 '17 at 16:42
  • 1
    Sum allows for 3 dimensional sum: `=SUM(Sheet1:Sheet5!X7)` This will sum all X7 in the sheets that are currently located between Sheet1 and Sheet5. So line up your sheets in order and add the new to the end then you just need `Worrksheets("Summary").Range("A1").Formula = "SUM(Sheet1:" & ws.Name & ")"` where `ws` is the worksheet variable of the new sheet added. – Scott Craner Oct 12 '17 at 16:49
  • 1
    @ScottCraner I think there's a minor typo in your formula (`Worrksheets`) :) – dwirony Oct 12 '17 at 16:54
  • @ScottCraner Ack that is inconvenient.. – dwirony Oct 12 '17 at 16:56
  • 1
    The suggestion above had some errors, but it is passed the time to edit so : `Worksheets("Summary").Range("A1").Formula = "SUM(Sheet1:" & ws.Name & "!X7)"` – Scott Craner Oct 12 '17 at 16:59

2 Answers2

0

As mentioned by Mooseman, you will not need to edit this formula, as long as any new sheets are between Sheet2 and SheetLast in your file.

But to address your specific question regarding the ability of a macro to modify the formula: yes, a macro can edit that formula. Assuming your formula is in a specific cell such as "A2", you could have a macro like this:

Sub MyMacro()
    Range("A2").Select
    ActiveCell.FormulaR1C1 ="=SUM(Sheet2!X7,Sheet3!X7,Sheet4!X7,Sheet5!X7)/F8"
End Sub
AndrewBanjo1968
  • 105
  • 8
  • As a tip: if you ever want to see if a Macro can do something, you can always "Record" a Macro and see what code it writes. In this case you would go to the "View" menu, choose Macros-->Record Macro. Then do whatever you'd like to see the code for (such as clicking in a cell and changing the formula). When done, go back to View-->Macros-->Stop Recording. Then from the same menu, you can "Edit" the Macro and see the VBA code that was written. It's very helpful tool for learning how to do things in VBA. Same works in Word, Access, PowerPoint, etc. – AndrewBanjo1968 Oct 12 '17 at 17:02
0

You can change the formula to always include any new sheets. You will just have to add the new sheet between the first and last sheet you are summing.

=SUM('Sheet2:SheetLast'!X7)/F8

You will not need to edit this formula, so long as any new sheets are between Sheet2 and SheetLast in your file.

mooseman
  • 1,997
  • 2
  • 17
  • 29