Automatic Macro to copy/past information when drop down value selected

[mikev2530]

I have a simple feature that I need to add to my Excel workbook:

I have two worksheets (“Prospects” and “Call Log”) each with "Company Name" and "Phone Number" columns. The first worksheet (“Prospects”) is used to identify sales prospects, and the second worksheet ("Call Log") tracks actual calls placed to those companies. On the "Prospects" worksheet, I have a “Called” column with drop down boxes for "N" and "Y" (default set to “N”). When “Y” is selected from the drop down box, a Macro copies the "Company Name" and "Phone Number" values for that row and pastes them into the corresponding row/cells ("Company Name" and "Phone Number") in the “Call Log” worksheet. (This is a simple shortcut to avoid manually entering company name/phone twice). The Macro also enters the current date and time on the “Call Log” worksheet and ends by selecting a cell on the same row for manual data entry by the sales person.

My problem is that I have my Macro defined for only one specific drop down box ("D6:E6") and corresponding row (“6”) for both worksheets. How do I create a Macro that will be universal for every drop down box and row, whereby changing the value from “N” to “Y” will automatically copy the company name/phone for that row in “Prospects” and paste it into the corresponding row/cells in “Call Log”?

Here’s my Macro:

Sub Macro1()
'
' Macro1 Macro
'

'
Range("D6:E6").Select
Selection.Copy
Sheets("Call Log").Select
Range("D6:E6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A6").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=NOW()"
Range("C6").Select
ActiveCell.FormulaR1C1 = "=NOW()"
Range("F6").Select
End Sub

Here's my VB script that automatically runs the Macro when "Y" is selected from the drop down box in row 6 of the "Prospects" worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "F6" And Target.Value = "Y" Then
Call Macro1
End If
End Sub

Obviously, this will also need to be modified so that it is universal, such that whenever a drop down box is changed to "Y" in "Prospects", the Macro automatically runs and copies the company name/phone for that row into the corresponding row/cells in "Call Log."

THANK YOU FOR YOUR HELP!