Excel VBA copy data for each new sheet

Question

I have an excel document.

My problem is: every time I create a new document, I need to move data. but also summarize the data automatically. Sheet 1 is my summaries.

Let's say I have a column named "sum", and in sheet2 I have a column price. If I set the price A1 = 1(int) in sheet 2, it shall be sent to the column sum in Sheet 1 (Summary). If I then copy sheet 2 and create sheet 3 and insert value 1 in the price of sheets 3 it will end up in Sheet 1 (Summary). the sum shuld then be 2 in sheet 1. is there any way to do this?

PolaEla PolaEla · Accepted Answer · 2014-09-25T12:33:57

Please place it in VBAProject -> ThisWorkbook. It will update itself every time you save file changes. If you want this to work for more than 1 cell, you just need to add loop in the place of comments.

Private Sub Workbook_AfterSave(ByVal Success As Boolean)

    Dim wb As Workbook
    Set wb = ActiveWorkbook

    Dim wbSize As Integer
    wbSize = wb.Sheets.Count

    Dim Sum As Long

    ' Open loop to apply to more than only 1 cell
    Sum = 0

        For i = 2 To wbSize

            Sum = Sum + wb.Sheets(i).Cells(1, 1).Value

        Next

    wb.Sheets(1).Cells(1, 1) = Sum
    ' Close the loop

End Sub

Excel VBA copy data for each new sheet

1 Answers