VBA - have a group of cells that update each other across multiple sheets

[ksscott]

Hi, I am working in Excel 365, on a spreadsheet with 6 tabs. (All 6 tabs feed off raw data in a 7th tab, but all 6 analyse different parts of the data, hence the separate tabs).

I have dropdown filters in the 1st tab which change the data in all 6 tabs . But I would like to be able to view and change these same filters in all 6 tabs, for a seamless experience.

The filters are in the same cells in all 6 tabs: C3, C4, E3, E4, E5, G3, G4, H3, H4.

After scouring various 2-way solutions on the internet I have managed to get a 2-way relationship going, with the following code:

Pasted in Sheet1: ('Geo - Chart')

Private Sub Worksheet_change(ByVal Target As Range)

If Not Intersect(Target, Range("C4")) Is Nothing Then
   If Target = Range("C4") Then
      Sheets("Geo - Table").Range("C4").Value = Target.Value
   End If
End If

If Not Intersect(Target, Range("C3")) Is Nothing Then
   If Target = Range("C3") Then
      Sheets("Geo - Table").Range("C3").Value = Target.Value
   End If
End If

If Not Intersect(Target, Range("E3")) Is Nothing Then
   If Target = Range("E3") Then
      Sheets("Geo - Table").Range("E3").Value = Target.Value
   End If
End If

If Not Intersect(Target, Range("E4")) Is Nothing Then
   If Target = Range("E4") Then
      Sheets("Geo - Table").Range("E4").Value = Target.Value
   End If
End If

If Not Intersect(Target, Range("E5")) Is Nothing Then
   If Target = Range("E5") Then
      Sheets("Geo - Table").Range("E5").Value = Target.Value
   End If
End If

If Not Intersect(Target, Range("C3")) Is Nothing Then
   If Target = Range("C3") Then
      Sheets("Geo - Table").Range("C3").Value = Target.Value
   End If
End If

If Not Intersect(Target, Range("G3")) Is Nothing Then
   If Target = Range("G3") Then
      Sheets("Geo - Table").Range("G3").Value = Target.Value
   End If
End If

If Not Intersect(Target, Range("G4")) Is Nothing Then
   If Target = Range("G4") Then
      Sheets("Geo - Table").Range("G4").Value = Target.Value
   End If
End If

If Not Intersect(Target, Range("H3")) Is Nothing Then
   If Target = Range("H3") Then
      Sheets("Geo - Table").Range("H3").Value = Target.Value
   End If
End If

If Not Intersect(Target, Range("H4")) Is Nothing Then
   If Target = Range("H4") Then
      Sheets("Geo - Table").Range("H4").Value = Target.Value
   End If
End If

End Sub

And in Sheet2: ('Geo - Table')

Private Sub Worksheet_change(ByVal Target As Range)

If Not Intersect(Target, Range("C4")) Is Nothing Then
  If Target = Range("C4") Then
    If Sheets("Geo - Chart").Range("C4").Value <> Target.Value Then
      Sheets("Geo - Chart").Range("C4").Value = Target.Value
    End If
  End If
End If

If Not Intersect(Target, Range("C3")) Is Nothing Then
  If Target = Range("C3") Then
    If Sheets("Geo - Chart").Range("C3").Value <> Target.Value Then
      Sheets("Geo - Chart").Range("C3").Value = Target.Value
    End If
  End If
 End If
 
If Not Intersect(Target, Range("E3")) Is Nothing Then
  If Target = Range("E3") Then
    If Sheets("Geo - Chart").Range("E3").Value <> Target.Value Then
      Sheets("Geo - Chart").Range("E3").Value = Target.Value
    End If
  End If
 End If
 
 If Not Intersect(Target, Range("E4")) Is Nothing Then
  If Target = Range("E4") Then
    If Sheets("Geo - Chart").Range("E4").Value <> Target.Value Then
      Sheets("Geo - Chart").Range("E4").Value = Target.Value
    End If
  End If
 End If
 
 If Not Intersect(Target, Range("E5")) Is Nothing Then
  If Target = Range("E5") Then
    If Sheets("Geo - Chart").Range("E5").Value <> Target.Value Then
      Sheets("Geo - Chart").Range("E5").Value = Target.Value
    End If
  End If
 End If
 
 If Not Intersect(Target, Range("G3")) Is Nothing Then
  If Target = Range("G3") Then
    If Sheets("Geo - Chart").Range("G3").Value <> Target.Value Then
      Sheets("Geo - Chart").Range("G3").Value = Target.Value
    End If
  End If
 End If
 
 If Not Intersect(Target, Range("G4")) Is Nothing Then
  If Target = Range("G4") Then
    If Sheets("Geo - Chart").Range("G4").Value <> Target.Value Then
      Sheets("Geo - Chart").Range("G4").Value = Target.Value
    End If
  End If
 End If
 
 If Not Intersect(Target, Range("H3")) Is Nothing Then
  If Target = Range("H3") Then
    If Sheets("Geo - Chart").Range("H3").Value <> Target.Value Then
      Sheets("Geo - Chart").Range("H3").Value = Target.Value
    End If
  End If
 End If
 
 If Not Intersect(Target, Range("H4")) Is Nothing Then
  If Target = Range("H4") Then
    If Sheets("Geo - Chart").Range("H4").Value <> Target.Value Then
      Sheets("Geo - Chart").Range("H4").Value = Target.Value
    End If
  End If
 End If
 

 
End Sub

(The code is long because I could only get it to work if I specify each filter cell separately; I could not get ranges to work, I got error messages). When I tried to copy this working code to other tabs, I was at a loss because with this method it seems all permutations of 2 tabs might need the same code. I have not been able to get it so that changing the filter on each of the 6 tabs updates the other tabs. Grateful for any help, thank you.