Forum Discussion
Creating a Roster
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.
- This is fantastic. I just started taking a comprehesive course on Excel. But I never would have been able to do this. Many thanks.
7 Replies
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 SubSwitch back to Excel.
Save the workbook as a macro-enabled workbook (*.xlsm).
Make sure that you allow macros when you open it.
- I've never done anything like this before. I entered the code in Sheet1. I believe I've done everything through saving the workbook. I'm not sure how to allow macros.
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.
