Complicated "Mirroring" of dynamic cels between worksheets...

SimmonsDeux

New Member
Joined
Dec 29, 2014
Messages
17
Phew...
Okay, guys:
I've got a Frankenstein of a workbook that keeps track of employees (Each employee has their own tab) and their incidents/errors/attendance issues.
There is a "LOG" (wsSource) sheet that I use to:
  • Input data (Reporting Date, who is reporting the incident, incident description, action taken, and key). There is an "Update" button that, when pressed, not only copies the data to the relevant employee sheet.. but also copies the data to a range that is specified on the "LOG" (wsSource) sheet. The sheet kind of acts as a mass dump of all of the entries.

I've achieved this via the following code (and help from several generous geniuses):

Code:
Sub CopyRow()
     Dim wsSource As Worksheet
     Dim wsDestin As Worksheet
     Dim lngDestinRow As Long
     Dim rngSource As Range
     Dim rngCel As Range
     Dim rngLog As Range
     
     Set wsSource = Sheets("LOG")
     Set wsDestin = Sheets(wsSource.Range("C5").Value)
     
     With wsSource
         'Column headers in Source worksheet so starts at row5
         Set rngSource = .Range("C5")
     End With
     
     For Each rngCel In rngSource
         If rngCel.Value = wsDestin.Name Then
             With wsDestin
                 'Accounts for column headers in Destination worksheet
                 lngDestinRow = .Cells(.Rows.Count, "C").End(xlUp).Offset(1, 0).Row
                 rngCel.EntireRow.Copy Destination:=wsDestin.Cells(lngDestinRow, "A")
             With wsSource
                 lngDestinRow = .Cells(.Rows.Count, "C").End(xlUp).Offset(1, 0).Row
                 rngCel.EntireRow.Copy Destination:=wsSource.Cells(lngDestinRow, "A")
             End With
            End With
         End If
    rngSource.EntireRow.ClearContents
     Next rngCel
End Sub

What I need is for the copied cels to be LINKED, meaning that if I modify the an entry from the "LOG" (wsSource) sheet, it updates in the destination sheet and vice versa. I am interested in value updates only. No formatting is necessary.

Let me know if I need to attach the workbook.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Hi,

If you need your cells to be "linked" ... you probably need to dynamically copy your updates via a worksheet event macro ...;)
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top