I have a form that I want to automate. I have several names in a drop down
box. I would like for each person named in the box to click on their name,
input their data, then send it to (populate) a spreadsheet list and close.
When the next person selects their name from the drop-down box, I want the
input form to be clear for their input and the whole process repeated.
Anybody know how I can accomplish this?
--
dc

Don't know if you can edit this one to suit your needs
when you say that you want your input cells to be clear afterwards, you will
need edit any cell addresses as needed.
This macro will look in cell E36, match it in column C (C493:C979)
then copy row 488 ( D488:BE488) next to what it has found ( usually a date,
if you leave E36 blank, it will select the next blank cell in Column C
between row 493 and 979.

U27:U31 is column that is cleared after input is pasted --- you WILL need to
edit these cells.



Sub SendData()
Dim myFind As Integer
Dim rng As Range
Dim rngToSearch As Range
Dim rngFound As Range
Set wks = ActiveSheet
Set rngToSearch = Worksheets("NameOfWorksheet").Range("C493:C979")
Set rngFound = rngToSearch.Find(What:=wks.Range("$E$36"), _
LookAt:=xlPart, MatchCase:=False)

If Not rngFound Is Nothing Then
Worksheets("WorksheetToCopyInto").Range("D488:BE48 8").Copy
rngFound.Offset(0, 1).PasteSpecial xlValues

Else
MsgBox myFind & " was not found"
End If
Range("U27:U31").Select
Selection.ClearContents
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

HTH

"Data Coordinator" wrote:

I have a form that I want to automate. I have several names in a drop down
box. I would like for each person named in the box to click on their name,
input their data, then send it to (populate) a spreadsheet list and close.
When the next person selects their name from the drop-down box, I want the
input form to be clear for their input and the whole process repeated.
Anybody know how I can accomplish this?
--
dc