Types of Private Sub

[smilelover]

I want to use my code personally on that workbook,
and I hope that it runs on the start up of the workbook and ANY changes on the workbook.

Currently, I've tried
Private Sub Workbook_Open()
Private Sub Workbook_Activate()

They both runs on start up of workbook and sync when I made any changes on EXISTING data, not when I add new data, for example, my code refers to column A on Sheet1 and column A on Sheet4, if I have 20 data on Sheet1!A and I made any changes on them, they will appear on Sheet4!A as well, but if I add an 21st data on Sheet1!A, they won't appear.

My code is :

Private Sub Workbook_Open()
    Dim rowA As Long
    For rowA = 1 To WorksheetFunction.CountA(Range("Sheet1!A:A"))
        Range("=Sheet4!A" & rowA * 8 - 5).Formula = ("=Sheet1!A" & rowA)
        Range("=Sheet4!J" & rowA * 8 - 5).Formula = ("=Sheet1!B" & rowA)
        Range("=Sheet4!K" & rowA * 8 - 5).Formula = ("=Sheet1!C" & rowA)
        Range("=Sheet4!L" & rowA * 8 - 5).Formula = ("=Sheet1!D" & rowA)
    Next rowA
    
End Sub

Private Sub Workbook_Activate()
    Dim rowB As Long
    For rowB = 1 To WorksheetFunction.CountA(Range("Sheet1!A:A"))
        Range("=Sheet4!B" & rowB * 8 - 5).Formula = ("=Sheet2!A1")
        Range("=Sheet4!C" & rowB * 8 - 4).Formula = ("=Sheet2!B2")
        Range("=Sheet4!D" & rowB * 8 - 3).Formula = ("=Sheet2!C3")
        Range("=Sheet4!E" & rowB * 8 - 2).Formula = ("=Sheet2!D4")
        Range("=Sheet4!F" & rowB * 8 - 1).Formula = ("=Sheet2!E5")
        Range("=Sheet4!G" & rowB * 8 - 0).Formula = ("=Sheet2!F6")
        Range("=Sheet4!H" & rowB * 8 + 1).Formula = ("=Sheet2!G7")
        Range("=Sheet4!I" & rowB * 8 + 2).Formula = ("=Sheet2!H8")
    Next rowB

Please tell me what kind of Private sub should I use

 

[RoryA]

You need the Worksheet_change event. It provides you one argument - Target is the cells being changed. You need to put the code into the code module for sheet1 - right click the sheet tab and choose view code.

 

[smilelover]

Hmm I keep getting an error:

Compile Error:
Procedure declaration does not match description of event or procedure having the same name.

Whats going on?

 

[RoryA]

You have declared it wrongly. Select Worksheet from the dropdown at the top left of the main code window and then select Change from the right hand dropdown list. That will create the declaration for you in the correct syntax:

private sub worksheet_change(byval target as excel.range)