Mirror or link cell to source cell when copying between sheets.

[mortless]

Here's the challange:

There is a macro that selects rows form active worksheet (base on one column content) and copy them into worksheet named Action Tracker. In addition also copying text from source sheet cell C4 into each newly copied row (column A).

Macro by Solus Rankin (many thanks!) goes like this:

Sub TransterToAT()

Dim cel As Range

For Each cel In Range("J1:J" & Range("J" & Rows.Count).End(xlUp).Row)
    If cel.Value = "a" Then
        Range("A" & cel.Row & ":I" & cel.Row).Copy
        With Sheets("ACTION TRACKER")
            .Range("A" & .Range("A" & Rows.Count).End(xlUp).Offset(1).Row).Value = ActiveSheet.Range("C4").Value
            .Range("B" & .Range("B" & Rows.Count).End(xlUp).Offset(1).Row).PasteSpecial
        End With
    End If
Next cel

Application.CutCopyMode = False

End Sub

What I am after is when making changes to the newly copied cells, those changed would be reflected in the source cells...and as an option vice versa. Another trick is that there is many source worksheets (named 1,2,3,4....).

I've got a feelling this is possible, however my VBA workshop is less than elementary So many ideas, music in my head...so what, while you dont know your instrument


Moderator Note:

Pls use code tags around your code next time as per forum rules.