Microsoft Office Forums

Go Back   Microsoft Office Forums > >

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #3  
Old 09-17-2024, 10:06 AM
NoSparks NoSparks is offline Need selected dropdown value in a cell of one sheet to populate same cell on other sheets Windows 10 Need selected dropdown value in a cell of one sheet to populate same cell on other sheets Office 2010
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 842
NoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of light
Default

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

Last edited by NoSparks; 09-17-2024 at 12:13 PM.
Reply With Quote
 



Similar Threads
Thread Thread Starter Forum Replies Last Post
Want to get the cell value on the bases of dropdown select. Want to copy the data from cell Puneet Singh Excel Programming 0 02-08-2022 11:04 AM
Need selected dropdown value in a cell of one sheet to populate same cell on other sheets How to paste the value of a selected cell to another sheet? Learner7 Excel Programming 3 06-30-2019 10:35 PM
Need selected dropdown value in a cell of one sheet to populate same cell on other sheets Using Cell Values in Active Sheet to Populate Path to External Reference Link wowcrofty Excel 4 12-19-2016 12:29 PM
Change cell color everytime a value is selected in dropdown list angelica_gloria Excel 4 01-27-2012 06:47 PM
Auto-populate an MS Word table cell with text from a diff cell? dreamrthts Word Tables 0 03-20-2009 01:49 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 06:44 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft