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.

[Solus Rankin]

Maybe

Sub TransterToAT()

Dim cel As Range
Dim ws As Worksheet

Set ws = ActiveSheet
For Each cel In ws.Range("J1:J" & Range("J" & Rows.Count).End(xlUp).Row)
    If cel.Value = "a" Then
        ws.Range("A" & cel.Row & ":I" & cel.Row).Copy
        With Sheets("ACTION TRACKER")
            .Activate
            .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 Link:=True
        End With
    End If
Next cel

Application.CutCopyMode = False

End Sub

[mortless]

Nope, does not accept Link:=True at all

[Solus Rankin]

Sub TransterToAT()

Dim cel As Range
Dim ws As Worksheet
Dim lRow As Long
Dim l As Long

Set ws = ActiveSheet
    For l = 2 To ws.Range("A" & Rows.Count).End(xlUp).Row
        If Range("J" & l).Value = "a" Then
            lRow = Sheets("ACTION TRACKER").Range("A" & Rows.Count).End(xlUp).Offset(1).Row
            Sheets("ACTION TRACKER").Cells(lRow, 1).Value = ws.Range("C4").Value
            For Each cel In ws.Range("A" & l & ":J" & l)
                With Sheets("ACTION TRACKER")
                    .Cells(lRow, cel.Column + 1).Formula = "=" & ws.Name & "!" & cel.Address
                End With
            Next cel
        End If
    Next l
End Sub

[mortless]

Hello and thanks for reply, really appreciate your help. The last code did something different from the original one, but not quite the right direction.

The best if I attach the file.

So this is what it suppose to do:

1. There is a macro that imports data from MS project and puts it in the new worksheets that is based on worksheet named "Template". Those new worksheets are named 1,2,3,4...and so on, representing data by "Event" - stated in cell C4 of each 1,2,3,4.. worksheet.
2. Now, for each worksheet (Event) work is done to "Description, Lessons and Actions:" section, then entries ticked of in column J are transfered to "ACTION TRACKER" worksheet by clicking on Transfer button. (code in Module2).
3. Once transferred, entries will be modified in "ACTION TRACKER" and the main feature would be to mirror all those changes in orginal, source worksheets. Ideally if this could work both ways.

As for now transfer works ok, only data validation is lost.

PS
If anyone wants to undertake this task, and can help me in developing this tool further, I am willing to grant a bonus

[Fotis1991]

Regarding your PS, i'd like to inform you that you can if you like to use the Commercial Service Sub forum. Take a look to this.

http://www.excelforum.com/the-water-...-services.html

[Solus Rankin]

I think I understand what you're trying to do. I don't think you want to link, if items are 'cleared' from ACTION TRACKER they will be deleted from the original sheet if they are link.

I think you will have a better time passing the line to the ACTION TRACKER, editing it and passing it back to the original sheet when edits are complete.

[mortless]

Yes, that would do. One potential problem I see in this case, what would happen to all the entries that were not transferred in the first place? Maybe if we sort entries just before transferring putting them on top of the list? In this way number of rows would match.
The challenge form me now is a macro that would find the right worksheet to copy entries back Guess base on Event
In my mind it would scan through all tabs searching fo Event in C4 cell?

[Solus Rankin]

Maybe AARlog (1).xlsm