Microsoft Office Forums - View Single Post - Need selected dropdown value in a cell of one sheet to populate same cell on other sheets

NoSparks · 09-17-2024, 10:06 AM

That doesn't quite work with my Excel 2010 (don't know about newer versions)

If an entry is made in a 'normal' cell it's fine but a selection via data validation drop down doesn't fill in on the other sheets.
I would need to use a macro in the 'ThisWorkbook' module
Something along the lines of this

Code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    Dim i As Long, sht, shtsArr
    
    '   only one cell ?
    If Target.CountLarge > 1 Then Exit Sub
    '   within chosen range ?
    If Intersect(Target, Sh.Range("C2:C20")) Is Nothing Then Exit Sub
    '   which sheets ?
    shtsArr = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6")
    '   is this a sheet in shtsArr ?
    For Each sht In shtsArr
        '   if so
        If Sh.Name = sht Then
            '   disable events
            Application.EnableEvents = False
            '   write to each sheets in shtsArr
            For i = 0 To UBound(shtsArr)
                Sheets(shtsArr(i)).Range(Target.Address) = Target.Value
            Next i
            '   re-enable events
            Application.EnableEvents = True
            '   no need to check further once found
            Exit For
        End If
    Next sht

End Sub