Excel – Is it possible to automatically add cells from a new sheet to a formula in Excel

financemicrosoft excelmicrosoft-excel-2013vbaworksheet-function

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.

Best Answer

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

Related Solutions

Excel – How to filter source sheet in Excel pivot table drill down

Not extremely straightforward. I've rebuilt code from a daily dose of excel to take advantage of the better filtering options of excel 2010. If you select a data point in your pivot and run the macro, it will give you the matching lines in your source data. It does so by using the Show Details function, then creating a filter for each column to match the data.

You can set it on a new right-click button, or overwrite the default show details behavior.

Private mPivotTable As PivotTable

Sub GetDetailsOnSource()

'turn off updates to speed up code execution
With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
End With

    On Error Resume Next
        Set mPivotTable = Selection.PivotTable
    On Error GoTo 0


   If Not mPivotTable Is Nothing Then
        If mPivotTable.PivotCache.SourceType <> xlDatabase Or _
            Intersect(Selection, mPivotTable.DataBodyRange) Is Nothing Then

            Set mPivotTable = Nothing
        End If
    End If

   Selection.ShowDetail = True
   GetDetailInfo

With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
End With

End Sub


Sub GetDetailInfo()

    Dim rCell As Range
    Dim rData As Range
    Dim vMin As Variant, vMax As Variant
    Dim rSource As Range
    Dim lOldCalc As Long, sh As Worksheet
    Dim colItems As Collection, arrFilter As Variant, lLoop As Long, lLastRow As Long
    Dim bBlanks As Boolean, bNumbers As Boolean, sNumberFormat As String

   Set sh = ActiveSheet

    If Not mPivotTable Is Nothing Then

        lOldCalc = Application.Calculation
        Application.Calculation = xlCalculationManual

        Set rSource = Application.Evaluate(Application.ConvertFormula(mPivotTable.SourceData, xlR1C1, xlA1))
        rSource.Parent.AutoFilterMode = False
        rSource.AutoFilter

       lLastRow = sh.ListObjects(1).Range.Rows.Count
       sh.ListObjects(1).Unlist

        'Loop through the header row

       For Each rCell In Intersect(sh.UsedRange, sh.Rows(1)).Cells

            If Not IsDataField(rCell) Then
                If Application.WorksheetFunction.CountIf(rCell.Resize(lLastRow), "") > 0 Then bBlanks = True Else bBlanks = False

                rCell.Resize(lLastRow).RemoveDuplicates Columns:=1, Header:=xlYes

                If Application.WorksheetFunction.CountA(rCell.EntireColumn) = Application.WorksheetFunction.Count(rCell.EntireColumn) + 1 _
                    And Not IsDate(sh.Cells(Rows.Count, rCell.Column).End(xlUp)) Then 'convert numbers to text
                    bNumbers = True
                    rCell.EntireColumn.NumberFormat = "0"
                    rCell.EntireColumn.TextToColumns Destination:=rCell, DataType:=xlFixedWidth, _
                        OtherChar:="" & Chr(10) & "", FieldInfo:=Array(0, 2), TrailingMinusNumbers:=True
                Else
                    bNumbers = False
                End If

                arrFilter = sh.Range(rCell.Offset(1), sh.Cells(sh.Rows.Count, rCell.Column).End(xlUp).Offset(IIf(bBlanks, 1, 0))).Value


                If Application.WorksheetFunction.Subtotal(3, rCell.EntireColumn) = 1 Then
                    rSource.AutoFilter Field:=rCell.Column, Criteria1:=""

                Else:
                    arrFilter = Application.Transpose(arrFilter)

                    sNumberFormat = rSource.Cells(2, rCell.Column).NumberFormat

                    If bNumbers Then _
                        rSource.Columns(rCell.Column).NumberFormat = "0"

                    rSource.AutoFilter Field:=rCell.Column, Criteria1:=arrFilter, Operator:=xlFilterValues

                    rSource.Cells(2, rCell.Column).NumberFormat = sNumberFormat
                End If

                Set arrFilter = Nothing
            End If

        Next rCell

        'so it doesn’t run at next sheet activate
       Set mPivotTable = Nothing

        Application.Calculation = lOldCalc

        'Delete the sheet created by double click
       Application.DisplayAlerts = False
            sh.Delete
        Application.DisplayAlerts = True

        rSource.Parent.Activate

    End If
End Sub

Private Function IsDataField(rCell As Range) As Boolean

    Dim bDataField As Boolean
    Dim i As Long

    bDataField = False
    For i = 1 To mPivotTable.DataFields.Count
        If rCell.Value = mPivotTable.DataFields(i).SourceName Then
            bDataField = True
            Exit For
        End If
    Next i

    IsDataField = bDataField

End Function

Excel – import data from cell automatically when a new tab is created

Something like this will create a list in sheet1 column A of Sheet Names, and every time cell C1 on any sheet changes, it will place the value of that cell into sheet1 in column B, next to the corresponding sheet name in column A

It goes in the ThisWorkbook module through the VBA editor*. You can change the sheet name and ranges as needed.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Not Intersect(Target, Sh.Range("C1")) Is Nothing Then
        Dim ws As Worksheet
        Set ws = Sheets("Sheet1")
        Dim iRow As Integer
        On Error GoTo Yikes:
TryAgain:
        iRow = Application.WorksheetFunction.Match(Sh.Name, ws.Range("A1:A50"), 0)
        ws.Cells(iRow, 2) = Target.Value
        Exit Sub

Yikes:

        Dim rowcount As Integer
        rowcount = ws.Cells(Rows.Count, 1).End(xlUp).Row
        Dim r As Range
        Set r = ws.Cells(rowcount + 1, 1)
        r = Sh.Name
        GoTo TryAgain:
    End If
End Sub

*To get to the VBA editor, you must go to the office button, excel options and in the popular tag, place a checkmark in the box next to Show Developer tab in the Ribbon. Then close that out, go to the developer tab and click the Visual Basic button on the far left. The editor will open up and you will need to double click the ThisWorkbook module on the left pane.

Best Answer

Related Solutions

Excel – How to filter source sheet in Excel pivot table drill down

Excel – import data from cell automatically when a new tab is created

Related Question