Double click to copy and paste value to first available row.

Hi Jim,

I posted some code below that copies the cells from column G to T whenever a cells in the range from G18:T43 is double-clicked per what I understood from your description. The worksheet is posted also so you can see how it works and if it is what you were looking for.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim i As Long
Dim sRow As Long

If Not Intersect(Target, Range("G18:T43")) Is Nothing Then
  sAddress = Target.Address
  i = InStr(2, Target.Address, "$")
  sRow = Right(Target.Address, Len(Target.Address) - i)

  ' Move Row
  For i = 9 To 15
    If IsEmpty(Range("G" & i)) Then
      Range("G" & sRow & ":T" & sRow).Copy
      Range("G" & i).Select
      ActiveSheet.Paste
      Target.Select
      GoTo 10
    End If
  Next i
  MsgBox "There are no empty rows from ""G9:G15"", nothing has been copied."
End If

10 End Sub

Thanks,

Daniel

Daniel,
Thanks for the response.

That is very close to what I am looking for but I am having a problem with the code. I pasted the code in my sheet and it almost works. By that I mean it will copy the format of the row but not the value. The section being copied is formated in a color and the data is displayed with formulas. The code copies the format but not the data. I dont care if the format is copied or not but it is the data I am trying to copy.

Also I will need to apply this to some other sheets in the workbook and I assume I can do so by adjusting the cell ranges as necessary.

Jim O

Daniel,

Iv'e got it working now. Thank you for pointing me in the right direction.

Jim O