macro to transfer info

[Guest]

I need a macro that will let me do this: When I click on a vendor ID number
in column A of sheet 1, I want all vendor information on that row to be
automatically copied to the next empty row on sheet 2, including the vendor
ID number, in the appropriate cells. Can you help? Thanks!

 

[Guest]

Ok, try this.

Replace what is in the Sheet1 code window with this
----------------------------------------------------------------------------------------
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim GoOn
'only activate if doubleclick in certain range
If Target.Column = 1 And Target.Value <> "" Then
GoOn = MsgBox("Copy Vendor Info?", vbOKCancel)
If GoOn <> vbOK Then
'if something other than "OK" is clicked, cancel
Exit Sub
Else
CopyVendorInfo
End If
End If

End Sub
-----------------------------------------------------------------------------------------
Replace what is in the Module with this
-----------------------------------------------------------------------------------------
Sub CopyVendorInfo()
Dim BlankCell As Integer

'copy area from activecell to cell 200 columns over
'you can change this by changing the number 200 in the next line
Range(ActiveCell, ActiveCell.Offset(0, 200)).Copy
'select the Field Activity Report sheet
Worksheets("Field Activity Report").Select
'find blank cell on the Field Activity Report sheet
BlankCell = Application.WorksheetFunction.CountA(Worksheets("Field Activity
Report").Range("D")) + 1
'select the next blank cell in column 4 (D)
Worksheets("Field Activity Report").Cells(BlankCell, 4).Select
'Paste
Selection.PasteSpecial (xlPasteValues)
'Get rid of those dashed lines
Application.CutCopyMode = False
'Select sheet1
Sheet1.Select
End Sub
--------------------------------------------------------------------------------------------
I added some comments to help you know what the code is doing too.

Hint:
There are two ways to refer to a sheet in VBA.
Worksheets("Name on the sheet tab") OR
SheetX

If you look in VBE on the left side it normally lists the sheets and modules
(you may need to click some of the little + signs to show them. For the
sheets, you will see Names like this:
Sheet2 (Field Activity Report)

You can refer to that sheet as either:
Worksheets("Field Activity Report") OR
Sheet2

It is preferable to use the 2nd method because it is less likely to be
changed by an end user. If you use the first method and they change the name

 

[Guest]

This did the trick. I tweeked a few syntax entries to properly position
everything and it works just fine. Thanks for all your help!