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.
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.![]()
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
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!![]()
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
Bookmarks