I'm trying to create code that mirrors specified cells in different sheets of the same Excel document. For example, I enter something in A1 in one sheet, I want it to be automatically entered at D6 in another sheet, and vice versa. This is the code I've been trying to adapt for my purposes:
In Sheet1 and Sheet2 modules:
Private Sub Worksheet_Change(ByVal Target As Range)
If Cells_Mirrored(Target) Then
'cells were mirrored
End If
End Sub
In a standard module:
Option Explicit
Function Cells_Mirrored(rTarget As Range) As Boolean
Dim aFrom()
Dim aTo()
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim rFrom As Range
Dim rTo As Range
Dim sFrom as String
Dim sTo as String
Dim a1()
Dim a2()
Dim i As Integer
If rTarget.Count = 1 Then
'*************************************
'Set these variables to what you need
Set sh1 = Worksheets("Sheet 1")
Set sh2 = Worksheets("Sheet 2")
With sh1
ReDim a1(1 To 4)
a1(1) = .Range("A1:A20")
a1(2) = .Range("A1")
a1(3) = .Range("B2")
a1(4) = .Range("D3")
End With
With sh2
ReDim a2(1 To 4)
a2(1) = .Range("B10:B30")
a2(2) = .Range("E4")
a2(3) = .Range("F5")
a2(4) = .Range("G6")
End With
'*************************************
If rTarget.Worksheet.Name = sh1.Name Then
aFrom() = a1()
aTo() = a2()
ElseIf rTarget.Worksheet.Name = sh2.Name Then
aFrom() = a2()
aTo() = a1()
Else
GoTo EF
End If
Application.EnableEvents = False
'Include next line Just in Case something happens
' You don't want to leave EnableEvents off
On Error Resume Next
For i = LBound(aFrom()) To UBound(aFrom())
Set rFrom = aFrom(i)
If Not Intersect(rTarget, rFrom) Is Nothing Then
Cells_Mirrored = True
Set rTo = aTo(i)
sFrom = rFrom.Worksheet.Name & "!" & rFrom.Address
sTo = rTo.Worksheet.Name & "!" & rTo.Address
If True Then
'This is here for debugging
' just change to "If False Then" when satisfied it works
Debug.Print sFrom; Spc(1); sTo
Stop
End If
Err.Clear
rFrom.Copy rTo
If Err.Number <> 0 Then
MsgBox sFrom & " was not mirrored in " & sTo
End If
End If
Next i
Application.EnableEvents = True
End If
EF:
End Function
This code is adapted from others - the code runs but doesn't mirror cells as intended. My programming aptitude modest at best so it's likely there is something very simple that I'm missing. Any help is appreciated!
Cheers,
Andrew
Bookmarks