Apr 21 2021 12:03 PM
Is there a way to automate building a roster (Roster worksheet) from the Drop Down list created (from Sheet1). I now cut and paste each name and place it in the cell as I select it. What I would like to do is when I select my first name from the drop down list, it automatically goes into Cell B6, the next name I select would go into B7, etc. The spreadsheet is attached.
Apr 21 2021 12:44 PM
Right-click the sheet tab.
Select 'View Code' from the context menu.
Copy the following code into the worksheet module:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long
If Not Intersect(Range("S5"), Target) Is Nothing Then
If Range("S5").Value <> "" Then
r = Range("B" & Rows.Count).End(xlUp).Row + 1
If r >= 31 Then
MsgBox "Roster is full!", vbExclamation
Else
Application.EnableEvents = False
Range("B" & r).Value = Range("S5").Value
Application.EnableEvents = True
End If
End If
End If
End Sub
Switch back to Excel.
Save the workbook as a macro-enabled workbook (*.xlsm).
Make sure that you allow macros when you open it.
Apr 21 2021 01:28 PM
Apr 21 2021 03:38 PM
The code should go into the worksheet module of Sheet2, since the dropdown is on that sheet.
See the attached version. If you get a warning that macros have been disabled, click to allow them.
Apr 21 2021 04:24 PM
SolutionApr 27 2021 12:52 PM
Apr 27 2021 01:09 PM
@marty007 That's an artefact. Simply remove the data validation rule from column B. The code doesn't add it.
Apr 21 2021 04:24 PM
Solution