VBA insert row : double click in a cell inserts row

  • Hello,


    I have a worksheet with a table with data information on the the following columns A:H. I need to assign a macro tthat do the following events: once the user select a cell from de column Aand and do a double click in the cell, the macro inserts a new row bellow the selected row with:
    1) same values in columns A:D
    2) empty values (only formats) in columns F:G
    3) formula in column H
    2) Column E with the value "1" in the row that was copy and in the new row


    I found in this forum the following macro that was great, I only added the code "If ActiveCell.Column = 1 Then" , but I do not know how a can insert the value "1" in column E, in the row that was copy and in the new row:



    Thank you in advanced

  • Hi Paul - does this work?


    Just one extra line towards the end of the macro.


    Ger

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

  • Hello Ger,


    After adpting your code to the original worksheet i came across with one problem...


    I have a large workbook with a lot of formulas and every time we change or insert data, by default, excel recalculates all the formulas and it takes a long time.
    In my workbook I have Sheet1 that I use to insert daily information (and is where I put your code), in Sheet2 I have almost all the formulas.


    To avoid this situation Ichoosen to set to manual calculation in the Workbook and in the Sheet1 I put the following code:


    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
      ActiveSheet.Calculate
    End Sub


    When I put your code (VBA insert row double click) and I double click the cells of column A, the code stops runnig and the following error appears in the debug window:


    Error 1004 - Paste Method of Worksheet Class Failed


    I am not very good with VBA...it is possible to help me?


    Thank you in advanced

  • Hmmm. honestly, not sure... that error looks unrelated to the one line of code that I added in (you could verify, by just commenting out the line that i added in


    Code
    'Cells(Target.Row, 5).resize(2).Value = 1

    In any case, my best guess here is that the activesheet part is causing a problem... so instead try this adapted version of the code which doesnt ".select" anything, or reference the activesheet. (In general in VBA Code there is no need ever to .Select anything anything, and very few situations where you need to refer to the activesheet).



    Final comment - setting calculation to manual is like taking the oil light out of the car dashboard to prevent it from coming on. Its rarely a good idea :) :) Might be time to rethink your formula/vba approach.


    Ger

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

  • Hello Ger,


    Your code has worked. The code is excelent and I have update my workbook with this solution, but now when we start to work in inserting the new rows I realize that the users need to insert the rows above the selected cell (not bellow)....I have been all day trying to adapt your code, but clearly i am a navy in vba and not have the knowledge find the solution...If you have disponibility, can you please help me one last time with this code?


    Thank you for all the help you already had given me...

  • This works I think....


    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

  • Hello Ger!


    Your code is just perfect!! Fantastic, It work works exactly as is intended, i made a test and I already adapted the workbook with this solution,


    A thousand thanks for your help and expertise!!!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!