Let's say I have a range of data in cells B1 thru Z100. In would like to
designate column A as the "selector" column, whereby a user could replicate
the chosen row by double-clicking in the column A next to the chosen row.
For example, if the user wants to replicate row 10, they would go to cell
A10, double-click, and it would copy/paste row 10 into row 11 (shifting the
other rows down to avoid overwriting them). Is this possible to do?

Right-click on the sheet's tab and select View Code. Copy this code and
paste it in there.
James

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Rows(Target.Row).Copy
Rows(Target.Row + 1).Insert
Application.CutCopyMode = False
Cancel = True
End Sub

"JDaywalt" wrote in message
...
Let's say I have a range of data in cells B1 thru Z100. In would like to
designate column A as the "selector" column, whereby a user could
replicate
the chosen row by double-clicking in the column A next to the chosen row.
For example, if the user wants to replicate row 10, they would go to cell
A10, double-click, and it would copy/paste row 10 into row 11 (shifting
the
other rows down to avoid overwriting them). Is this possible to do?

Hi,

Try this

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
Target.EntireRow.Select
Selection.Copy
Target.Offset(1, 0).Select
Selection.Insert Shift:=xlDown
End If
End Sub

Right click sheet tab|view code and paste in

Mike

"JDaywalt" wrote:

Let's say I have a range of data in cells B1 thru Z100. In would like to
designate column A as the "selector" column, whereby a user could replicate
the chosen row by double-clicking in the column A next to the chosen row.
For example, if the user wants to replicate row 10, they would go to cell
A10, double-click, and it would copy/paste row 10 into row 11 (shifting the
other rows down to avoid overwriting them). Is this possible to do?

here's one way:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim lLastRow As Long
If Target.Column = 1 Then
lLastRow = Range("A65535").End(xlUp).Row + 1
Target.EntireRow.Copy Destination:=Range("A" & lLastRow)
Cancel = True
End If
End Sub


--
Hope that helps.

Vergel Adriano


"JDaywalt" wrote:

Let's say I have a range of data in cells B1 thru Z100. In would like to
designate column A as the "selector" column, whereby a user could replicate
the chosen row by double-clicking in the column A next to the chosen row.
For example, if the user wants to replicate row 10, they would go to cell
A10, double-click, and it would copy/paste row 10 into row 11 (shifting the
other rows down to avoid overwriting them). Is this possible to do?

correction. I missed that you wanted to copy it to the next row and shift
everything down.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Target.Column = 1 Then
Target.Offset(1).EntireRow.Rows.Insert
Target.EntireRow.Copy Destination:=Target.Offset(1)
Cancel = True
End If
End Sub


--
Hope that helps.

Vergel Adriano


"Vergel Adriano" wrote:

here's one way:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim lLastRow As Long
If Target.Column = 1 Then
lLastRow = Range("A65535").End(xlUp).Row + 1
Target.EntireRow.Copy Destination:=Range("A" & lLastRow)
Cancel = True
End If
End Sub


--
Hope that helps.

Vergel Adriano


"JDaywalt" wrote:

Let's say I have a range of data in cells B1 thru Z100. In would like to
designate column A as the "selector" column, whereby a user could replicate
the chosen row by double-clicking in the column A next to the chosen row.
For example, if the user wants to replicate row 10, they would go to cell
A10, double-click, and it would copy/paste row 10 into row 11 (shifting the
other rows down to avoid overwriting them). Is this possible to do?

I have tried each of the suggested methods, however, when I double click the
cells in column A, it throws me into the standard cell edit mode (it assumes
I want to edit the cell contents.) Am I missing something?

"JDaywalt" wrote:

Let's say I have a range of data in cells B1 thru Z100. In would like to
designate column A as the "selector" column, whereby a user could replicate
the chosen row by double-clicking in the column A next to the chosen row.
For example, if the user wants to replicate row 10, they would go to cell
A10, double-click, and it would copy/paste row 10 into row 11 (shifting the
other rows down to avoid overwriting them). Is this possible to do?

did you omit the line that says "Cancel = True" ?

--
Hope that helps.

Vergel Adriano


"JDaywalt" wrote:

I have tried each of the suggested methods, however, when I double click the
cells in column A, it throws me into the standard cell edit mode (it assumes
I want to edit the cell contents.) Am I missing something?

"JDaywalt" wrote:

Let's say I have a range of data in cells B1 thru Z100. In would like to
designate column A as the "selector" column, whereby a user could replicate
the chosen row by double-clicking in the column A next to the chosen row.
For example, if the user wants to replicate row 10, they would go to cell
A10, double-click, and it would copy/paste row 10 into row 11 (shifting the
other rows down to avoid overwriting them). Is this possible to do?

I copied the code exactly. Could the problem be that I have text in that
cell? I have the words "dbl-click" in each cell of column A to remind the
user that they are supposed to double-click the cell to replicate the row.
Here is an example of what I'm talking about:

A B C
1 (Header) (Header) (Header)
2 dbl-click Mr. Jones 1239
3 dbl-click Mrs. Smith 45678



"Vergel Adriano" wrote:

did you omit the line that says "Cancel = True" ?

--
Hope that helps.

Vergel Adriano


"JDaywalt" wrote:

I have tried each of the suggested methods, however, when I double click the
cells in column A, it throws me into the standard cell edit mode (it assumes
I want to edit the cell contents.) Am I missing something?

"JDaywalt" wrote:

Let's say I have a range of data in cells B1 thru Z100. In would like to
designate column A as the "selector" column, whereby a user could replicate
the chosen row by double-clicking in the column A next to the chosen row.
For example, if the user wants to replicate row 10, they would go to cell
A10, double-click, and it would copy/paste row 10 into row 11 (shifting the
other rows down to avoid overwriting them). Is this possible to do?

you also need to make sure you paste the code in the worksheet code
module...follow the instructions given by Zone and Mike H.


--
Hope that helps.

Vergel Adriano


"JDaywalt" wrote:

I copied the code exactly. Could the problem be that I have text in that
cell? I have the words "dbl-click" in each cell of column A to remind the
user that they are supposed to double-click the cell to replicate the row.
Here is an example of what I'm talking about:

A B C
1 (Header) (Header) (Header)
2 dbl-click Mr. Jones 1239
3 dbl-click Mrs. Smith 45678



"Vergel Adriano" wrote:

did you omit the line that says "Cancel = True" ?

--
Hope that helps.

Vergel Adriano


"JDaywalt" wrote:

I have tried each of the suggested methods, however, when I double click the
cells in column A, it throws me into the standard cell edit mode (it assumes
I want to edit the cell contents.) Am I missing something?

"JDaywalt" wrote:

Let's say I have a range of data in cells B1 thru Z100. In would like to
designate column A as the "selector" column, whereby a user could replicate
the chosen row by double-clicking in the column A next to the chosen row.
For example, if the user wants to replicate row 10, they would go to cell
A10, double-click, and it would copy/paste row 10 into row 11 (shifting the
other rows down to avoid overwriting them). Is this possible to do?

The only other thing is that events may be turned off. To turn them on,
with the code editor on the screen, press Ctrl-g to open the immediate pane.
Type in the immediate pane
application.enableevents=true
and press Enter, then try again. James
"Vergel Adriano" wrote in message
...
you also need to make sure you paste the code in the worksheet code
module...follow the instructions given by Zone and Mike H.


--
Hope that helps.

Vergel Adriano


"JDaywalt" wrote:

I copied the code exactly. Could the problem be that I have text in that
cell? I have the words "dbl-click" in each cell of column A to remind
the
user that they are supposed to double-click the cell to replicate the
row.
Here is an example of what I'm talking about:

A B C
1 (Header) (Header) (Header)
2 dbl-click Mr. Jones 1239
3 dbl-click Mrs. Smith 45678



"Vergel Adriano" wrote:

did you omit the line that says "Cancel = True" ?

--
Hope that helps.

Vergel Adriano


"JDaywalt" wrote:

I have tried each of the suggested methods, however, when I double
click the
cells in column A, it throws me into the standard cell edit mode (it
assumes
I want to edit the cell contents.) Am I missing something?

"JDaywalt" wrote:

Let's say I have a range of data in cells B1 thru Z100. In would
like to
designate column A as the "selector" column, whereby a user could
replicate
the chosen row by double-clicking in the column A next to the
chosen row.
For example, if the user wants to replicate row 10, they would go
to cell
A10, double-click, and it would copy/paste row 10 into row 11
(shifting the
other rows down to avoid overwriting them). Is this possible to
do?

I don't know about the code sent by others but with mine if you right click
the sheet tab, view code and paste it in then it does what you require.

Mike

"JDaywalt" wrote:

I have tried each of the suggested methods, however, when I double click the
cells in column A, it throws me into the standard cell edit mode (it assumes
I want to edit the cell contents.) Am I missing something?

"JDaywalt" wrote:

Let's say I have a range of data in cells B1 thru Z100. In would like to
designate column A as the "selector" column, whereby a user could replicate
the chosen row by double-clicking in the column A next to the chosen row.
For example, if the user wants to replicate row 10, they would go to cell
A10, double-click, and it would copy/paste row 10 into row 11 (shifting the
other rows down to avoid overwriting them). Is this possible to do?

I just realized ... When I right-clicked the sheet tab to View Code, then did
the copy/paste, I didn't realize that the drop-down at the top of the VB
module was set to "General" vs. "Worksheet". I changed the selector to
"Worksheet", then recopied the code & everything worked fine. Thanks for all
your help!!

"JDaywalt" wrote:

Let's say I have a range of data in cells B1 thru Z100. In would like to
designate column A as the "selector" column, whereby a user could replicate
the chosen row by double-clicking in the column A next to the chosen row.
For example, if the user wants to replicate row 10, they would go to cell
A10, double-click, and it would copy/paste row 10 into row 11 (shifting the
other rows down to avoid overwriting them). Is this possible to do?

Right click on the worksheet tab, choose View Code, and paste in the
following code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim RowNum As Long
If Target.Cells.Count 1 Then
Exit Sub
End If
If Target.Column < 1 Then
Exit Sub
End If
Me.Rows(Target.Row + 1).Insert
Me.Rows(Target.Row).Resize(2).FillDown
Cancel = True
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"JDaywalt" wrote in message
...
Let's say I have a range of data in cells B1 thru Z100. In would like to
designate column A as the "selector" column, whereby a user could
replicate
the chosen row by double-clicking in the column A next to the chosen row.
For example, if the user wants to replicate row 10, they would go to cell
A10, double-click, and it would copy/paste row 10 into row 11 (shifting
the
other rows down to avoid overwriting them). Is this possible to do?