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 · Accepted Answer · 2014-09-25 12:33:57Z

0

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

answered Sep 25, 2014 at 12:33

PolaEla

1164 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user2053451 Over a year ago

I tryed to copy the contctent of sheet2 and create sheet 3. in the sum cell of shete 1(summary) this is the code: =Sheet2!J73 i tryed to add your code but it didnt update. and taht is probly because of = sheet2!J73.

PolaEla Over a year ago

Did you save file? As you can see Excel run the code every time you save changes. Also, there is no need to add anything anywhere except the code to the "ThisWorkbook" module.

PolaEla Over a year ago

You can always change 'Workbook_AfterSave(ByVal Success As Boolean)' to 'Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)'. This will make excel update itself every time you search through spreadsheets. But with large amount of data it can slow down whole application.

user2053451 Over a year ago

Think the problme is that, when i copy sheet2 to sheet3. i send the the price integer to sheet 1 like this. =sheet2!J73. If i then copy the sheet it dosent have any refrances to Sheet1. only sheet2 have taht refrance.

Collectives™ on Stack Overflow

Excel VBA copy data for each new sheet

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related