CODEWITHSUNDEEP
excelvba
2acb_John_Suren
2acb_John_Suren

Reputation: 49

How to populate a cell in a different sheet based on the value in another sheet and vice versa

I am trying to use VBA so that I can input a value in cell B7 in sheet2 and then it would automatically populate in C7 in sheet3 and also work vice versa. I tried the code below and couldn't get it to work, any suggestions? Also would the code be the same for a string of a number?

Private Sub Worksheet_Change(ByVal Target As Range)
  On Error GoTo eh
  If Not Intersect(Target, ThisWorkbook.Sheets("sheet 2").Range("B7")) Is Nothing Then
    Application.EnableEvents = False
    ThisWorkbook.Sheets("sheet 3").Range("C" & Target.Row - 0).Value = Target.Value
eh:
Application.EnableEvents = True
    If Err <> 0 Then MsgBox Err & " " & Err.Description, , "Error in Worksheet_Change event, sheet 2"
  End If
  
End Sub

Upvotes: 2

Views: 110

Answers (1)

VBasic2008
VBasic2008

Reputation: 54777

A Workbook SheetChange: Same Value in Cells of Worksheets

  • Note that the code needs to be copied to the ThisWorkbook module.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
     
    Dim wsNames As Variant: wsNames = VBA.Array("sheet 2", "sheet 3")
    Dim CellAddresses As Variant: CellAddresses = VBA.Array("B7", "C7")
     
    Dim iCell As Range
    Dim n As Long
    
    For n = 0 To UBound(wsNames)
        If StrComp(Sh.Name, wsNames(n), vbTextCompare) = 0 Then
            Set iCell = Intersect(Sh.Range(CellAddresses(n)), Target)
            If Not iCell Is Nothing Then
                Application.EnableEvents = False
                    Me.Worksheets(wsNames(1 - n Mod 2)) _
                        .Range(CellAddresses(1 - n Mod 2)).Value = iCell.Value
                Application.EnableEvents = True
            End If
            Exit For
        End If
    Next n

End Sub

Upvotes: 3

Related Questions