Posts by jujubean6402

    Re: Loop Through Data Validation List

    I forgot to mention that I'm using Excel 2010 and my email application is Lotus Notes 8.5. Not sure the latter matters, but I noticed on some other threads there are differences in functionality between various versions of Excel.

    I have an Excel workbook that is use to keep track of employee attendance. One of the sheets, EE Attendance Calendar, has a Data Validation list in cell A4. The list is of employee names and is pulled from a named range of cells in different sheet, Employee Data, in the same workbook (named range is Name and refers to 'Employee Data'!$A$3:$A$102). When an employee’s name is selected, other cells in the EE Attendance Calendar sheet pull data for that employee from other sheets in the workbook.

    I wanted to email a portion of the EE Attendance Calendar sheet to each employee, so I decided to create a macro I could assign to a button. I know very little about VBA, but after some internet research and using the Record Macro function I managed to piece together code that: copies cells from EE Attendance Calendar and pastes them into a different sheet in a new workbook, completes some formatting, enters formulas into certain cells, saves the new workbook, emails it to the employee, and then deletes the new workbook.

    The part I'm have a problem with is doing all the above for each employee in the Data Validation list with one click of a button. I found this thread with a seemingly similar question:…highlight=validation+list

    I tried to insert the suggested code in my code, but I'm not getting the desired result. When I run the macro, it creates the new file and sends the email for the first employee listed over and over again. I’m pretty sure it would go all day long if I let it. I end up having to use Task Manager to kill Excel. Not sure if I'm not inserting the code in the proper place or if my situation requires different code to handle looping through the names on the list.

    I also found this thread, but I’m not sure how to incorporate the code.…ighlight=macro+validation

    My code where I tried to incorporate the code in the first thread is below. I can upload the workbook, but I will need to delete employee data. So, thought I would try this way first.

    Let me know if you need any additional information. Your guidance is greatly appreciated!