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!

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("F ield Activity
Report").Range("D: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
on the sheet tab, the code no longer works. Hope this helps.


"Larry" wrote:

Man, this is really close. It does exactly what I asked about. However, I
neglected to provide specific information you should have had. Sheet2 is
named 'Field Activity Report'. I renamed it to Sheet2 to make the macro
work, but it really needs to be called "Field Activity Report'. Information
copied onto the next blank row of 'Field Activity Report' needs to begin on
column d, not column a. Once the user has pressed OK on the message box, the
macro needs to go back to sheet1 automatically after the information is
entered onto the 'Filed Activity Report'. If you can help me tweek this, it
would be greatly appreciated. You did a great job! Thanks!

"Brassman" wrote:

Try this:

Copy this code into the code window for Sheet1
-----------------------------------------------------------------------------------------
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim GoOn
If Target.Column = 1 And Target.Value < "" Then
GoOn = MsgBox("Copy Vendor Info?", vbOKCancel)
If GoOn < vbOK Then
Exit Sub
Else
CopyVendorInfo
End If
End If

End Sub
-----------------------------------------------------------------
Copy this code into a regular module
-----------------------------------------------------------------------------------------
Sub CopyVendorInfo()
Dim BlankCell As Integer

ActiveCell.EntireRow.Copy
Sheet2.Select
BlankCell = Application.WorksheetFunction.CountA(Sheet2.Range( "A:A")) + 1
Sheet2.Cells(BlankCell, 1).Select
Selection.PasteSpecial (xlPasteValues)
Application.CutCopyMode = False

End Sub
------------------------------------------------------------------------------------------
Maybe not the best way to do it, but it works.
"Larry" wrote:

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!

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

"Brassman" wrote:

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("F ield Activity
Report").Range("D: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
on the sheet tab, the code no longer works. Hope this helps.


"Larry" wrote:

Man, this is really close. It does exactly what I asked about. However, I
neglected to provide specific information you should have had. Sheet2 is
named 'Field Activity Report'. I renamed it to Sheet2 to make the macro
work, but it really needs to be called "Field Activity Report'. Information
copied onto the next blank row of 'Field Activity Report' needs to begin on
column d, not column a. Once the user has pressed OK on the message box, the
macro needs to go back to sheet1 automatically after the information is
entered onto the 'Filed Activity Report'. If you can help me tweek this, it
would be greatly appreciated. You did a great job! Thanks!

"Brassman" wrote:

Try this:

Copy this code into the code window for Sheet1
-----------------------------------------------------------------------------------------
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim GoOn
If Target.Column = 1 And Target.Value < "" Then
GoOn = MsgBox("Copy Vendor Info?", vbOKCancel)
If GoOn < vbOK Then
Exit Sub
Else
CopyVendorInfo
End If
End If

End Sub
-----------------------------------------------------------------
Copy this code into a regular module
-----------------------------------------------------------------------------------------
Sub CopyVendorInfo()
Dim BlankCell As Integer

ActiveCell.EntireRow.Copy
Sheet2.Select
BlankCell = Application.WorksheetFunction.CountA(Sheet2.Range( "A:A")) + 1
Sheet2.Cells(BlankCell, 1).Select
Selection.PasteSpecial (xlPasteValues)
Application.CutCopyMode = False

End Sub
------------------------------------------------------------------------------------------
Maybe not the best way to do it, but it works.
"Larry" wrote:

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!