0

I'm very new to VBA coding (only dabbling for this specific project), so I would really appreciate any advice on an issue I'm having. I'm attempting to independently link multiple column ranges on Sheet1 (e.g., A2:A300, B2:B300) to various different column ranges on Sheet2, Sheet3, etc. This way, changing any of the individual cells in these ranges will change the cells in the corresponding sheet and vice versa. ]I'm not doing anything fancy with the data being linked - it just needs to auto-update. I currently only managed to successfully link one set of column ranges. This is what I have on Sheet1:

Private Sub Worksheet_Change(ByVal Target As Range)
  On Error GoTo eh
  If Not Intersect(Target, Me.Range("B2:B300")) Is Nothing Then
    Application.EnableEvents = False
    Sheets("Sheet2").Range("C" & Target.Row).Value = Target.Value
eh:
    Application.EnableEvents = True
    If Err <> 0 Then MsgBox Err & " " & Err.Description, , "Error in Worksheet_Change event, Sheet1"
  End If
End Sub

And this is what I have for Sheet2:

Private Sub Worksheet_Change(ByVal Target As Range)
  On Error GoTo eh
  If Not Intersect(Target, Me.Range("C2:C300")) Is Nothing Then
    Application.EnableEvents = False
    Sheets("Sheet1").Range("B" & Target.Row).Value = Target.Value
eh:
    Application.EnableEvents = True
    If Err <> 0 Then MsgBox Err & " " & Err.Description, , "Error in Worksheet_Change event, Sheet2"
  End If
End Sub

So my question is how do I expand on this? How do I link Sheet3, Sheet4, etc. to different columns of Sheet1 or get multiple columns from Sheet1 linked on Sheet2. Is it possible to modify the existing command in Sheet1 to refer to these additional sheets or would it need a separate command? Thank you so much!

5
  • Just to be clear, your situation doesn't allow you to use formulas to reference cells on other sheets?
    – bugdrown
    Commented Apr 14 at 22:57
  • Thank you for responding! I only found a way using formulas to mirror what's on another sheet (so a Sheet2 would reflect a Sheet1 column, but not the other way around).
    – thtrmus
    Commented Apr 15 at 0:56
  • Would it be possible to have a master sheet and have other sheets reference it via formulas, rather than trying to update all sheets via vba?
    – bugdrown
    Commented Apr 15 at 16:34
  • I can't understand your exact situation, but it sounds like either you need to 1) add more if statements, 2) add in elseif statements, or 3) switch to case statement (which is an alternative to 2)
    – gns100
    Commented Apr 15 at 19:19
  • Thank you both! I will experiment will all of these options.
    – thtrmus
    Commented Apr 15 at 20:39

0

You must log in to answer this question.

Browse other questions tagged .