double click macro for table

  • Hello,


    I have a worksheet with a table with data information on the the following columns A:H. I need to assign a macro to the Worksheet that will perform the following events: once the user selects a cell from column B and double clicks in that cell, the macro inserts a new row bellow the selected row with:

    1) same values in columns A:N of selected row

    2) Delete values (only keep formats) in columns F:H



    I found in this forum the following macro that was great, but I do not know how a can get the macro to run inside the table. i feel like i need to reference the (Table 3 ) but cant figure out where in the statement to properly put it

    1. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    2. If ActiveCell.Column = 1 Then
    3. 'David McRitchie, 2007-09-07 insrtrow.htm on double-click
    4. '-- will copy more often than Extend Formulas and Format (tools option)
    5. Cancel = True
    6. Target.EntireRow.Copy
    7. Cells(Target.Row + 1, 1).EntireRow.Insert
    8. Cells(Target.Row + 1, 1).EntireRow.Select
    9. ActiveSheet.Paste
    10. Application.CutCopyMode = False
    11. On Error Resume Next
    12. '-- customize range for what cells constants can be removed --
    13. Intersect(Selection, Range("F:H")).SpecialCells(xlConstants).ClearContents
    14. On Error GoTo 0
    15. Else
    16. End If
    17. End Sub
  • Hello,


    You could test the following


    Code
    1. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    2. If Target.Column <> 2 Then Exit Sub
    3. Rows(Target.Row + 1 & ":" & Target.Row + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    4. Cancel = True
    5. End Sub


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • so its 50% perfect as it does insert a line below the intended line selected by double clicking a cell in Column 2 ( column B )


    it is a complete blank line as apposed to coping all the data from Column A:N then deleting the content only not the format of Column F:H for the given row selected


    your help and simplification of the Macro was most appreciated

  • this is were i struggle as i'm trying to work with VBA at a level well above my knowledge base


    i'm taking an online course for this but my current needs exceed my learning plan


    long way around to saying how do you change your code to copy everything from the selected Row and then only delete the content not the format of the cells in the row F:H only

  • Hello,


    Below is the requested modification


    Hope this will help

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)