+ Reply to Thread
Results 1 to 2 of 2

VBA for Multiple Two Way Mirrored Cells

Hybrid View

  1. #1
    Registered User
    Join Date
    02-03-2017
    Location
    California
    MS-Off Ver
    Mac 2004 and Mac 2008
    Posts
    2

    VBA for Multiple Two Way Mirrored Cells

    I need to create a spreadsheet where every cell is mirrored with a cell in another worksheet. I am told that the formatting must stay the same for the most part. Each row is related to another row and each column in the "Database" is meant to represent a different worksheet with the same data. You can find an example file in the attachments. There are many more rows and columns, but I figured that it would be easier if I simplified the spreadsheet. So far, I created a version where if you press "Update" button on the sheet a macro will copy and paste the new information into the allocated cells.

    Sub Rectangle2_Click()
    
    Range("B6").Copy Worksheets("Database").Range("C2")
    Range("B7").Copy Worksheets("Database").Range("C4")
    Range("B8").Copy Worksheets("Database").Range("C1")
    Range("B9").Copy Worksheets("Database").Range("C3")
    Range("B10").Copy Worksheets("Database").Range("C5")
    Range("B11").Copy Worksheets("Database").Range("C6")
    
    End Sub
    Sub Rectangle3_Click()
    
    Range("C2").Copy Worksheets("Routine Chest Contrast - GE").Range("B6")
    Range("C4").Copy Worksheets("Routine Chest Contrast - GE").Range("B7")
    Range("C1").Copy Worksheets("Routine Chest Contrast - GE").Range("B8")
    Range("C3").Copy Worksheets("Routine Chest Contrast - GE").Range("B9")
    Range("C5").Copy Worksheets("Routine Chest Contrast - GE").Range("B10")
    Range("C6").Copy Worksheets("Routine Chest Contrast - GE").Range("B11")
    
    End Sub
    It's very clunky and I know there should be a more efficient way to code cells that automatically mirror triggered by a change in the worksheet. I've seen code on this forum and many others like it where people mirror cells by named tables, but I haven't found a way to adapt the code so that this VBA could work with multiple cells or multiple named tables. The VBA only works for one area at a time, and because of the formatting of the spreadsheet, I would need for it to perform the same function but for multiple areas.

    Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim colNum As Long, rowNum As Long
    
        If Not Intersect(Target, [area_2]) Is Nothing Then
            Application.EnableEvents = False
            [area_1].Value = [area_2].Value
        End If
        
        Application.EnableEvents = True
    End Sub
    I've just learned basic VBA over the last few days and I don't completely understand the syntax yet. But I would like assistance in either adapting my clunky first code to trigger with a change in the worksheet instead of buttons or assistance in adapting the second code to fit my particular needs. Thank you very much for your time!
    Attached Files Attached Files

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,957

    Re: VBA for Multiple Two Way Mirrored Cells

    I am assuming that you want to mirror Input1 onto Input2.

    The following code entered as a module on the Input1 sheet should do it. More about the code in red later.
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim MyRange As String
    
    Dim sh As Worksheet
    
    MyRange = "$B$6:$B$11,$B$13:$B$14,$B$16:$B$19,$B$21:$B$23,$E$6:$E$18,$E$20:$E$27"
    If Intersect(Target, Range(MyRange)) Is Nothing Then Exit Sub
    
    Target.Copy Sheets("Input2").Range(Target.Address)
    
    End Sub
    There is a macro called CaptureRange in the workbook. Carefully select (using the CTRL key) all the cells you want to be a part of the mirroring. Then with the cells still selected, run this macro. It will write the list of cells into Cell A1 on Sheet1. Copy and paste this range of cells into the red area in the code. I only did a partial list. If you make a mistake, you could edit the code manually or do a recapture.
    Sub CaptureRange()
    Sheets("Sheet1").Range("A1") = Selection.Address
    End Sub
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Run-time error '13' Type mismatch on multiple two way mirrored cells code
    By Excel Rose in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 12-11-2016, 10:25 AM
  2. Two-way mirrored cells question
    By jdeviant in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-10-2016, 09:05 PM
  3. Linked/mirrored Data Validation
    By mcayea in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-12-2014, 01:09 PM
  4. Mirrored cells
    By Aivaras in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-15-2013, 04:33 AM
  5. Adding rows when cells are cloned/mirrored?
    By kmuirhead in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 08-31-2011, 06:01 AM
  6. Building a mirrored notification sheet?
    By Bscott05 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-18-2010, 08:36 PM
  7. mirrored array/matrix
    By hierarchii in forum Excel General
    Replies: 3
    Last Post: 08-05-2005, 06:05 AM

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