VBA to copy data from multiple cells between sheets

[Hazman_08]

I have a workbook with 2 sheets. Sheet1 has range D19:I22. Sheet2 has range E5:J8. What I want to do is have data entered into one cell copied to a specific other cell on the other sheet, and vice versa. For example, Sheet1 D19 copied to Sheet2 E5, or Sheet2 E5 copied to Sheet1 D19. I think this would be a Worksheet_Change of some sort. Can someone please tell me if/how this can be done?

 

[kevin9999]

You would need 2 Worksheet Change event modules, one for sheet1, one for sheet 2. I'll assume you know how/where to add sheet module codes, and please note that I've used the sheet code names (e.g. Sheet1) not the sheet names (tab names).

This one in the sheet 1 module:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("D19:I22"), Target) Is Nothing Then
        On Error GoTo Escape
        Application.EnableEvents = False
        Target.Copy Sheet2.Range(Target.Address).Offset(-14, 1) '<-- *** Sheet code name used ***
    End If
Continue:
    Application.EnableEvents = True
    Exit Sub
Escape:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue
End Sub

This one in the sheet 2 module:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("E5:J8"), Target) Is Nothing Then
        On Error GoTo Escape
        Application.EnableEvents = False
        Target.Copy Sheet1.Range(Target.Address).Offset(14, -1) '<-- *** Sheet code name used ***
    End If
Continue:
    Application.EnableEvents = True
    Exit Sub
Escape:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue
End Sub

 

[Hazman_08]

Thanks Kevin. The sheet names have spaces in them. How do I handle that when replacing "Sheet1" and "Sheet2"? When I type without spaces, I get this error

 

[kevin9999]

Thanks Kevin. The sheet names have spaces in them. How do I handle that when replacing "Sheet1" and "Sheet2"? When I type without spaces, I get this error
View attachment 98026

You don't use the sheet names, you use the code names. Don't change the code as posted at all. You can change Sheet1 to anything you like (e.g. "Sheet 1 of 2") but the code name does not change.

 

[Hazman_08]

Have done as instructed. Data entered in Sheet1 D4 does not copy to Sheet2 E5. Have tried other cells. No result.

 

[kevin9999]

Have done as instructed. Data entered in Sheet1 D4 does not copy to Sheet2 E5. Have tried other cells. No result.

D4? Where does that fit with your described range in post #1?

 

[Hazman_08]

D4? Where does that fit with your described range in post #1?

Sorry, meant D19.

 

[Hazman_08]

So, basically, D19 should copy to E5, D20 to E6, D21 to E7, etc, on Sheet 2 if entered in Sheet 1 range. If entered in Sheet 2 range, should be E5 to D19 on Sheet 1, E6 to D20, E7 to D21 etc.

 

[Hazman_08]

Success. It works. Thank you so much.

 

[kevin9999]

Glad we got there in the end