Mirror range of cells in different sheets?

I would like to mirror a range of cells between two sheets in the same workbook, so that if the data is manually input into one of the cells in range (E5:H11) in 'Sheet 1', it is automatically updated in the corresponding cell in (H33:K39) of 'Sheet 2', and vice versa.

How can I make this happen? More details than not might be helpful since I'm new to VBA.

Using Office 2013. Thanks!

It same post
http://www.mrexcel.com/forum/excel-q...nt-sheets.html

Try this

Sub Mirror()
    Worksheets("Sheet2").Range("H33:K39").Value = Worksheets("Sheet1").Range("E5:H11").Value
End Sub

See attached solution - this is what I did:

1- Name the areas you want to sync (I named them area_1 and area_2)

2- Use worksheet_change() events on both sheets to control what happens when one sheet is altered.

code for worksheet 1:

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)

Dim colNum As Long, rowNum As Long

    If Not Intersect(Target, [area_1]) Is Nothing Then
      colNum = Target.Column - [area_1].Cells(1, 1).Column + 1
      rowNum = Target.Row - [area_1].Cells(1, 1).Row + 1
      
      Application.EnableEvents = False
      [area_2].Cells(rowNum, colNum).Value = Target.Value
    End If
    
    Application.EnableEvents = True
End Sub

code for worksheet 2:

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)

Dim colNum As Long, rowNum As Long

    If Not Intersect(Target, [area_2]) Is Nothing Then
      colNum = Target.Column - [area_2].Cells(1, 1).Column + 1
      rowNum = Target.Row - [area_2].Cells(1, 1).Row + 1
      
      Application.EnableEvents = False
      [area_1].Cells(rowNum, colNum).Value = Target.Value
    End If
    
    Application.EnableEvents = True
End Sub

Huh, i guess you could just mirror the entire table each time like marreco is showing - but you'll still need to "fire" that command when a worksheet change is made. You can leave in the control "If Not Intersect(Target, [area_2]) Is Nothing Then" just to keep it from firing when a change is made to other parts of the worksheet (outside of the tables you mentioned).

hmm. Well that's interesting.

I think we could just borrow from marreco's idea and mirror the entire table each time.

The problem is with this line: [area_1].Cells(rowNum, colNum).Value = Target.Value

[area_1].Cells(rowNum, colNum) is always one cell, where Target (as you just pointed out) can me many cells.

All we need to do is equate the values for the areas - we still need to control each event so it happens both directions, and we need to toggle application events on and off to avoid being stuck in a loop. But the code should just look like this:

Option Explicit


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