Results 1 to 1 of 1

Mirror multiple cells in different sheets

Threaded View

  1. #1
    Registered User
    Join Date
    06-18-2012
    Location
    Salt Lake City, UT
    MS-Off Ver
    Excel 2010
    Posts
    1

    Mirror multiple cells in different sheets

    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
    Last edited by abarney; 06-19-2012 at 10:29 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1