2

I would like to make a new financial spreadsheet for tracking expenses. Since the expense sheets are created as and when I cannot pre-generate a spreadsheet to populate with formulae. I have a summary sheet which adds totals from the other sheets and currently create a formula by hand at the end of the month. Is there a way to automatically sum across multiple sheets and add new sheets to the sum when they are created?

Is this possible using standard Excel or am I going to need to use some VBA?

I know how to add the same cell across multiple sheets already (see: Excel - Formulas that total across multiple sheets?)

This vba script is part of the way to what I am looking to do but not exactly what I want to do: Excel - import data from cell automatically when a new tab is created

P.S. I don't know VBA but willing to learn. I'm used to C/Python.

Improve this question
8
  • If you are summing "cookie-cutter" ranges then you could insert sheets named "begin" and "end" before and after all the sheets you wish to consolidate, then use =SUM(begin:end!A1), and make sure that you always add sheets in between the "begin" and "end" sheets.
    – Lord Peter
    Feb 20, 2015 at 11:24
  • That is what I was considering as a quick and easy solution. Probably what I will implement for March. Still hoping there would be a way to automate most of this. Essentially I want to make a fairly bullet proof version for an end user that I rarely have to edit.
    – Tom Wilkinson
    Feb 20, 2015 at 11:28
  • Why doesn't that solution work for you? Are your totals on each sheet in different locations?
    – Raystafarian
    Feb 20, 2015 at 14:23
  • It works for me personally. I would prefer, however, to come up with a more foolproof system so that if given to someone else who doesn't create a new sheet between the existing ones it would still calculate the correct values. That aside I'm also interested in whether this could be achieved from a learning Excel standpoint.
    – Tom Wilkinson
    Feb 20, 2015 at 14:59
  • I'm new to VBA - can you point me in the direction of the correct resources or provide a working script?
    – Tom Wilkinson
    Feb 20, 2015 at 17:02

1 Answer 1

Reset to default
1

Say, for instance, you have your running total in Sheet1!A1. Right, so now you create a macro you can run that will take all the totals from the other sheets and sum them.

Let's assume the totals for each sheet are on cell A10 -

Sub updatethesum()
Dim ws As Worksheet
    Dim i As Double
    i = 0
For Each ws In ThisWorkbook.Worksheets

    If ws.Name <> "Sheet1" Then
        i = i + ws.Range("A10")
    End If

Next
Sheets("Sheet1").Range("A1") = i
End Sub

If you're worried someone will put a letter instead of a number in A10 you can restrict the input with something like this -

Sub updatethesum()

Dim ws As Worksheet
    Dim i As Double
    i = 0
    Dim bletter As Boolean

For Each ws In ThisWorkbook.Worksheets

    If ws.Name <> "Sheet1" Then
        bletter = IsNumeric(ws.Range("A10").Value)
            If bletter = True Then
             i = i + ws.Range("A10")
            End If
    End If

Next
Sheets("Sheet1").Range("A1") = i
End Sub
Improve this answer
3
  • Works like a charm! Now to test it on my end users
    – Tom Wilkinson
    Feb 20, 2015 at 17:22
  • @tom just a note, this doesn't leave any evidence that the total sum is correct. You could, instead, have i be a string that continuously adds each sheet as a reference and insert it as a formula, then you'd have evidence.
    – Raystafarian
    Feb 20, 2015 at 17:50
  • Another point - for financial calculations this doesn't work as Long rounds the numbers. Changing Long to Double fixes this.
    – Tom Wilkinson
    Feb 23, 2015 at 16:09

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .